Separate Text into numbers and alphabets
 

I have strings in Col A which has few numbers then few alphabets and then
again a few numbers.

Eg.
123ABCygh97t945729572
1B6
341gagjKHGL8793

I want to separate the first set of numbers in Col B, set of alphabets in
Col C, and the second set of numbers in Col C.

I want to do this using formulae...

Separate Text into numbers and alphabets
 

On Tue, 9 Sep 2008 17:45:01 -0700, Sheeloo
wrote:

I have strings in Col A which has few numbers then few alphabets and then
again a few numbers.

Eg.
123ABCygh97t945729572
1B6
341gagjKHGL8793

I want to separate the first set of numbers in Col B, set of alphabets in
Col C, and the second set of numbers in Col C.

I want to do this using formulae...

Your first example has 5 segments. How do you want that divided?

If you just have three segments <digits<letters<digits

You could use:

A1: Original string

B1 and C1 must be entered as array formulas (hold down <ctrl<shift while
hitting <enter)

B1: =LEFT(A1,-1+MATCH(FALSE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:255")),1)),0))

C1:
=LEFT(SUBSTITUTE(A1,B1,"",1),-1+MATCH(TRUE,ISNUMBER(
-MID(SUBSTITUTE(A1,B1,"",1),ROW(INDIRECT("1:255")), 1)),0))

D1: =SUBSTITUTE(A1,B1&C1,"",1)

But if your first example was not a typo, then the formula in D1 will need to
change.
--ron

Separate Text into numbers and alphabets
 

Thanks Ron,

Yes, first string had a typo.

The reason I thought of this was because in another post Mike (in the post -
Alphanumeric Sorting - numeric alpha numeric) had asked about sorting a range
of values which have three segments like I mentioned.

Once they are separated sorting is easy.

Thanks for your help..


"Ron Rosenfeld" wrote:

On Tue, 9 Sep 2008 17:45:01 -0700, Sheeloo
wrote:

I have strings in Col A which has few numbers then few alphabets and then
again a few numbers.

Eg.
123ABCygh97t945729572
1B6
341gagjKHGL8793

I want to separate the first set of numbers in Col B, set of alphabets in
Col C, and the second set of numbers in Col C.

I want to do this using formulae...

Your first example has 5 segments. How do you want that divided?

If you just have three segments <digits<letters<digits

You could use:

A1: Original string

B1 and C1 must be entered as array formulas (hold down <ctrl<shift while
hitting <enter)

B1: =LEFT(A1,-1+MATCH(FALSE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:255")),1)),0))

C1:
=LEFT(SUBSTITUTE(A1,B1,"",1),-1+MATCH(TRUE,ISNUMBER(
-MID(SUBSTITUTE(A1,B1,"",1),ROW(INDIRECT("1:255")), 1)),0))

D1: =SUBSTITUTE(A1,B1&C1,"",1)

But if your first example was not a typo, then the formula in D1 will need to
change.
--ron

Separate Text into numbers and alphabets
 

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html
then use the following formulas

In B1: =TRIM(LEFT(REGEX.SUBSTITUTE(A1,"[A-Z,a-z]",REPT(" ",99)),99))
In C1: =REGEX.SUBSTITUTE(A1,"[0-9]","")
In D1: =SUBSTITUTE(A1,B1&C1,"")


"Sheeloo" wrote:

I have strings in Col A which has few numbers then few alphabets and then
again a few numbers.

Eg.
123ABCygh97t945729572
1B6
341gagjKHGL8793

I want to separate the first set of numbers in Col B, set of alphabets in
Col C, and the second set of numbers in Col C.

I want to do this using formulae...

Separate Text into numbers and alphabets
 

On Tue, 9 Sep 2008 19:39:00 -0700, Teethless mama
wrote:

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html
then use the following formulas

In B1: =TRIM(LEFT(REGEX.SUBSTITUTE(A1,"[A-Z,a-z]",REPT(" ",99)),99))
In C1: =REGEX.SUBSTITUTE(A1,"[0-9]","")
In D1: =SUBSTITUTE(A1,B1&C1,"")


Perhaps simpler if you are going to use the morefunc add-in:

B1: =REGEX.MID(A1,"\d+")
C1: =REGEX.MID(A1,"\D+")

D1: =REGEX.MID(A1,"\d+",2)

--ron