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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
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,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Maybe


Excel 2010
ABC
1abc 123abc123
2defgg jkl 123defgg jkl123
3PACK PLATE 65445 535PACK PLATE65445 535
Sheet7
Cell Formulas
RangeFormula
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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,034
Office Version
  1. 2019
  2. 2010
Platform
  1. 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
38,034
Office Version
  1. 2019
  2. 2010
Platform
  1. 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
4,142
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
38,034
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,175,657
Messages
5,898,726
Members
434,725
Latest member
D3njy

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