Separate Numbers from text

[undemane]

In column A, I have cells with text and numbers (example: abc12446, xyz14456, etc.). I want to separate these into column B cell with abc, and another column with 12446 in it. How do I do this?

 

[yky]

Is it always three letters followed by five digits?

 

[wideboydixon]

Do you want the numbers as a string (Column C) or as a number (Column D):

Book1
	A	B	C	D
1	abc12446	abc	12446	12446
2	monkeys001	monkeys	001	1
3	xyz14456	xyz	14456	14456
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"))-1)
C1	=MID($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789")),LEN($A1))
D1	=MID($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789")),LEN($A1))+0

WBD

 

[Peter_SSs]

Do you want the numbers as a string (Column C) or as a number (Column D):

Simpler for C & D would be
=SUBSTITUTE(A1,B1,"")
=SUBSTITUTE(A1,B1,"")+0


Though it would be good to know the answer to yky's question and how consistent/varied the data actually is.

 

[wideboydixon]

Simpler for C & D would be
=SUBSTITUTE(A1,B1,"")
=SUBSTITUTE(A1,B1,"")+0

Of course. Doh.

WBD

 

[undemane]

The letters can be 3 or more characters. Not consistent.

 

[wideboydixon]

The letters can be 3 or more characters. Not consistent.

As above then:

Book1
	A	B	C	D
1	abc12446	abc	12446	12446
2	monkeys001	monkeys	001	1
3	xyz14456	xyz	14456	14456
4	a2	a	2	2
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"))-1)
C1	=SUBSTITUTE($A1,$B1,"")
D1	=SUBSTITUTE($A1,$B1,"")+0

WBD

 

[Jonmo1]

The letters can be 3 or more characters. Not consistent.

What about the Numbers? That could be usefull if it's known that it's always 5 numbers at the end.

In that case you could do this

Unknown
	A	B	C
1	abc12446	abc	12446
2	vwxyz14456	vwxyz	14456
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,C1,"")
B2	=SUBSTITUTE(A2,C2,"")
C1	=RIGHT(A1,5)+0
C2	=RIGHT(A2,5)+0

 

[Rick Rothstein]

...if it's known that it's always 5 numbers at the end.

In that case you could do this
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=SUBSTITUTE(A1,C1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

If you are right about the number always being 5 digits, then I think I would use this formula instead of the one above (my gut tells me LEFT and LEN should be more efficient functions than SUBSTITUTE)...

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