Thanks:  0
Likes:  0

# Thread: Separate text and numbers

1. ## Separate text and numbers

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

2. ## Re: Separate text and numbers

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

3. ## Re: Separate text and numbers

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

4. ## Re: Separate text and numbers

Maybe

Excel 2010
ABC
1abc 123abc123
2defgg jkl 123defgg jkl123
3PACK PLATE 65445 535PACK PLATE65445 535

Sheet7

Array Formulas
CellFormula
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)}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

5. ## Re: Separate text and numbers

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)

6. ## Re: Separate text and numbers

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

7. ## Re: Separate text and numbers

Originally Posted by 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

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

8. ## Re: Separate text and numbers

Originally Posted by 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)
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

9. ## Re: Separate text and numbers

Originally Posted by Momentman
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•