Separate text and numbers

[ravikr1980]

Hi,

I want to separate the text and numbers into two different cells. Basically, I have the entire drawing details in one cell and I need to split it up, e.g. I have:

[TABLE="width: 316"]
<colgroup><col></colgroup><tbody>[TR]
[TD]KEEP PLATE 902 4 0002[/TD]
[/TR]
[TR]
[TD]STOOL 525 4 0199[/TD]
[/TR]
[TR]
[TD]PACK PLATE 525 4 0200[/TD]
[/TR]
[TR]
[TD]PACK PLATE 525 4 0201[/TD]
[/TR]
[TR]
[TD]PACK PLATE 525 4 0202[TABLE="width: 316"]
<colgroup><col></colgroup><tbody>[TR]
[TD]plz help[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

Are they all structured as TEXT followed by numbers? meaning you need KEEP PLATE, STOOL, PACK PLATE e.t.c right?

 

[DILIPandey]

Hi Ravi,

Try using below formula, enter using ctrl shift enter key combination:-

=MID(A1,MIN(IFERROR(ISNUMBER(MID(A1,ROW(1:99),1)*1)*(SEARCH(MID(A1,ROW(1:99),1)*1,A1)),"")),99)

where a1 is given text.

Regards,
DILIPandey

 

[Momentman]

Maybe

Excel 2010
	A	B	C
1	abc 123	abc	123
2	defgg jkl 123	defgg jkl	123
3	PACK PLATE 65445 535	PACK PLATE	65445 535
Sheet7

Cell Formulas
Range	Formula
B1	{=LEFT(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0)-2)}
C1	{=RIGHT(A1,LEN(A1)-MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0)+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

Give these formulas a try...

For the Text: =TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

For the Numbers: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

 

[ravikr1980]

I want text and number in two different cell

For example

PACK PLATE 525 4 0200

Result i want:-

Pack plate in one cell and 525 4 0200 in another cell

 

[Rick Rothstein]

I want text and number in two different cell

For example

PACK PLATE 525 4 0200

Result i want:-

Pack plate in one cell and 525 4 0200 in another cell

Our messages may have crossed each other... see Message #5

 

[Momentman]

Give these formulas a try...

For the Text: =TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

For the Numbers: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

Nicely done as always Rick. I guess the reason you did A1&"0123456789" was to avoid #VALUE where some numbers were not found in the text being searched. Brilliant.

I dont know how you do it, but you just always do it..........now am laughing at my huge array formula in message #4

 

[Rick Rothstein]

Nicely done as always Rick. I guess the reason you did A1&"0123456789" was to avoid #VALUE where some numbers were not found in the text being searched.

Thank you for your nice comment. And yes, that is exactly the reason for concatenating the numbers onto the value in the cell... to give the FIND function something to find. This protects against a cell's text not having a number, but it also protects against a cell having no text meaning you can copy the formula down past your existing data in preparation for future data that may be added below the currently existing data.