Separate Text into numbers and alphabets

[Sheeloo]

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...

 

[Ron Rosenfeld]

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

 

[Sheeloo]

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..

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/3000-2077_4-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,"")

 

[Ron Rosenfeld]

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/3000-2077_4-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