Alphanumeric

Possible improvement...

In your sample data all the number strings are the same length. So, if
that's the case then we can use much simpler formulas.

In B2:

=LEFT(A2,LEN(A2)-5)

In C2:

=RIGHT(A2,5) (returns a TEXT number)
=--RIGHT(A2,5) (returns a NUMERIC number)

The same condition applies to leading 0s.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Based on your posted sample...assuming the data starts in A2.

Enter this formula in B2:

=SUBSTITUTE(A2,C2,"")

Note that this will return a TEXT value. If you want the number to be a
numeric number then try this:

=--SUBSTITUTE(A2,C2,"")

However, if your number string starts with leading 0s this will strip them
off. If you number strings are all the same length (as in your sample
data) and will have leading 0s then you can use the "--" version and then
custom number format to retain the leading 0s.

Enter this formula in C2:

=LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

Select both B2 and C2 and copy down as needed.

--
Biff
Microsoft Excel MVP


"beejay" wrote in message
...
Hi there Mike

with regard to this alphanumeric problem, i have my own version of
problem

let say i have one colimn that contain:
column A
p12345
tr58967
drt45862

and i want to seperate the numbers and letters
and will look like this

column A column B column C
p12345 p 12345
tr58967 tr 58964
drt45862 drt 45862

what type of function or formula i can use to do this task

thank you for the information
it works



"T. Valko" wrote:

Possible improvement...

In your sample data all the number strings are the same length. So, if
that's the case then we can use much simpler formulas.

In B2:

=LEFT(A2,LEN(A2)-5)

In C2:

=RIGHT(A2,5) (returns a TEXT number)
=--RIGHT(A2,5) (returns a NUMERIC number)

The same condition applies to leading 0s.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Based on your posted sample...assuming the data starts in A2.

Enter this formula in B2:

=SUBSTITUTE(A2,C2,"")

Note that this will return a TEXT value. If you want the number to be a
numeric number then try this:

=--SUBSTITUTE(A2,C2,"")

However, if your number string starts with leading 0s this will strip them
off. If you number strings are all the same length (as in your sample
data) and will have leading 0s then you can use the "--" version and then
custom number format to retain the leading 0s.

Enter this formula in C2:

=LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

Select both B2 and C2 and copy down as needed.

--
Biff
Microsoft Excel MVP


"beejay" wrote in message
...
Hi there Mike

with regard to this alphanumeric problem, i have my own version of
problem

let say i have one colimn that contain:
column A
p12345
tr58967
drt45862

and i want to seperate the numbers and letters
and will look like this

column A column B column C
p12345 p 12345
tr58967 tr 58964
drt45862 drt 45862

what type of function or formula i can use to do this task

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"beejay" wrote in message
news
thank you for the information
it works



"T. Valko" wrote:

Possible improvement...

In your sample data all the number strings are the same length. So, if
that's the case then we can use much simpler formulas.

In B2:

=LEFT(A2,LEN(A2)-5)

In C2:

=RIGHT(A2,5) (returns a TEXT number)
=--RIGHT(A2,5) (returns a NUMERIC number)

The same condition applies to leading 0s.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Based on your posted sample...assuming the data starts in A2.

Enter this formula in B2:

=SUBSTITUTE(A2,C2,"")

Note that this will return a TEXT value. If you want the number to be a
numeric number then try this:

=--SUBSTITUTE(A2,C2,"")

However, if your number string starts with leading 0s this will strip
them
off. If you number strings are all the same length (as in your sample
data) and will have leading 0s then you can use the "--" version and
then
custom number format to retain the leading 0s.

Enter this formula in C2:

=LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

Select both B2 and C2 and copy down as needed.

--
Biff
Microsoft Excel MVP


"beejay" wrote in message
...
Hi there Mike

with regard to this alphanumeric problem, i have my own version of
problem

let say i have one colimn that contain:
column A
p12345
tr58967
drt45862

and i want to seperate the numbers and letters
and will look like this

column A column B column C
p12345 p 12345
tr58967 tr 58964
drt45862 drt 45862

what type of function or formula i can use to do this task