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>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,118
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
4,118
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
37,343
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
37,343
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
4,118
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
37,343
Office Version
  1. 2019
  2. 2010
Platform
  1. 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.
 

Forum statistics

Threads
1,141,062
Messages
5,704,062
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top