How to split only the numbers into separate cells?

**Droriley** · 11-18-2014, 10:45 AM

I have table with cells of that kind:

Work Phone:+2133388480
Work Phone:+2133388481
Work Phone:+2133388482
Work Fax:+2133388483

Mobile:+3752961155033
Work Phone:+3751743449381
Work Phone:34466031
Work Fax:+3751573446022
Work Fax:29965758

Work Phone:+86 760 88789266

Every "brick" is taking place in one cell. as you can see there is different number of entries in each cell, as well as different types.
I wish to have 3 columns eventually: Work phone, Fax, Mobile.
Can you help me with matching function?
Thanks to all,
Riley

**pjwhitfield** · 11-18-2014, 03:47 PM

Please Login or Register  to view this content.

**Bailey_Thomas** · 11-18-2014, 03:51 PM

The work-around I would use is a Find/Replace function...
I'd select the column with the data>Find/Replace (CTRL+F)>Click on the "Replace" tab>Enter "*:" (this will delete all information before the number)>copy the column that now has JUST phone numbers>create a new workbook>paste the numbers to the D column>Go back to original workbook>UNDO the Find/Replace>CTRL+F again and type in ":*" (that will delete all numbers)>copy the column that now has type of number>go to the workbook I had you create and paste in column A>from there you can do a filter>sort by type of number>copy/paste into new column.
That will give you a final worksheet that has column A: Work Phone:, Column B: Mobile:, Column C: Work Fax:, Column D: numbers.

Hope that helps a little!

Kind Regards,

Thomas Bailey

**semajjames** · 11-18-2014, 03:51 PM

this could work for you

HOW TO ONLY READ NUMBERS IN CELL CONTAINING LETTERS & NUMBERS =LEFT(A1,FIND(" ",A1)-1)

**FDibbins** · 11-18-2014, 05:04 PM

semajjames, all that does is pull in all characters (both text and numbers) up to the 1st space?

Try this, copied down. It will get most of what you want
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

**AlKey** · 11-18-2014, 05:58 PM

For this type of extraction it is more practical to have return numbers as text rather than real numbers.

This formula will extract numbers with "+" sign

=TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",255)),255))

and this one without "+"

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",255)),255)),"+","")

Row\Col	A	B	C
1	Phone/Fax Number	With "+" sign	Without "+", sign
2	Work Phone:+2133388480	+2133388480	2133388480
3	Work Phone:+2133388481	+2133388481	2133388481
4	Work Phone:+2133388482	+2133388482	2133388482
5	Work Fax:+2133388483	+2133388483	2133388483
6	Mobile:+3752961155033	+3752961155033	3752961155033
7	Work Phone:+3751743449381	+3751743449381	3751743449381
8	Work Phone:34466031	34466031	34466031
9	Work Fax:+3751573446022	+3751573446022	3751573446022
10	Work Fax:29965758	29965758	29965758

**Droriley** · 11-19-2014, 03:01 AM

Hi alkey,
It seems like you're the closest to solution. I might have not been totally clear. All the first 4 rows are in one cell (work phone--->Work Fax). Same for next 5 (Mobile---->Work Fax).
this data was derived from busuiness cards, hence not repeating in the very same patern.
what I could use is function of that kind:
If there is "Work Phone" - a function that would take it out (I don't mind using the first "Work Phone" entry
If there is "Mobile" - same
If there is "work Fax" - same

I believe it is easier to use 3 different functions for 3 different columns.
By the way your function output was always the last number in a cell.

Thanks a lot for your attempts,
Riley

Business Cards.xlsx