Separate text and numbers

ravikr1980

New Member
Joined
Jan 11, 2014
Messages
2
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:

KEEP PLATE 902 4 0002
STOOL 525 4 0199
PACK PLATE 525 4 0200
PACK PLATE 525 4 0201
PACK PLATE 525 4 0202
plz help

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
3,979
Office Version
365
Platform
Windows
Are they all structured as TEXT followed by numbers? meaning you need KEEP PLATE, STOOL, PACK PLATE e.t.c right?
 

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,979
Office Version
365
Platform
Windows
Maybe

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">abc 123</td><td style=";">abc</td><td style="text-align: right;color: #333333;;">123</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">defgg jkl 123</td><td style=";">defgg jkl</td><td style="text-align: right;color: #333333;;">123</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">PACK PLATE 65445 535</td><td style=";">PACK PLATE</td><td style="color: #333333;;">65445 535</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">{=LEFT(<font color="Blue">A1,MATCH(<font color="Red">TRUE,ISNUMBER(<font color="Green">1*MID(<font color="Purple">A1,ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:" & LEN(<font color="Navy">A1</font>)</font>)</font>),1</font>)</font>),0</font>)-2</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">{=RIGHT(<font color="Blue">A1,LEN(<font color="Red">A1</font>)-MATCH(<font color="Red">TRUE,ISNUMBER(<font color="Green">1*MID(<font color="Purple">A1,ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:" & LEN(<font color="Navy">A1</font>)</font>)</font>),1</font>)</font>),0</font>)+1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
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

New Member
Joined
Jan 11, 2014
Messages
2
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,979
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,301
Messages
5,449,531
Members
405,569
Latest member
deanro

This Week's Hot Topics

Top