Splitting phone numbers out into different cells

[laundon]

Hello! Bit of advice needed as I'm running out of hair to pull!

I have a column which contains a landline number, a mobile number, or in some cases both. I need to strip these out into separate columns. The common identifier for each example is the word at the start of the cell. If the cell contains a mobile number it starts "Mobile: 0788 666666" and if it is a landline number it starts "Phone: 0121 898989" etc.

I've tried various options to solve my headache and keep coming up with nothing.

It's kind of an advanced "Text to Columns" but I can't figure it out!
Any ideas you lovely people?


Cheers,
Tim.

 

[jtakw]

Hi,

You're not clear on How you want the strings Split, and you didn't give any samples of "some cases both", we'll start with this:

Book1
	A	B
1	Mobile: 0788 666666	0788 666666
2	Phone: 0121 898989	0121 898989
Sheet34

Cell Formulas
Range	Formula
B1	=TRIM(MID(A1,FIND(":",A1)+1,255))

B1 formula copied down.

 

[hotabae]

Could you please provide us with an example of what this might look like in a sheet?

I think it would help if you could include a sample of what the data looks like now, and how you would like it to be organized afterwards.

Please include examples of how you would like each case handled:

1. Mobile
2. Landline
3. Both / Multiple

 

[Rick Rothstein]

Try putting this formula in the Mobile column...

=IFERROR(MID(A1,SEARCH("Mobile:",A1)+8,11),"")

and this formula in the Phone column...

=IFERROR(MID(A1,SEARCH("Phone:",A1)+7,11),"")

 

[laundon]

Sorry, my bad.

Cell A1 may contain 2 different numbers:
Phone: 01254 909090
Mobile: 07970 123456"

Cell A2 May contain 1 number:
Phone: 01381 123456

and Cell A3 may contain a different type of number:
Mobile: 07999 121212

As a side issue, how can I paste actual cell references from Excel like you all did above?

 

[jtakw]

So do you want them split into different columns??
One column for Mobile, another for Phone??

 

[Rick Rothstein]

Sorry, my bad.

Cell A1 may contain 2 different numbers:
Phone: 01254 909090
Mobile: 07970 123456"

Cell A2 May contain 1 number:
Phone: 01381 123456

and Cell A3 may contain a different type of number:
Mobile: 07999 121212

Did the formulas I posted in Message #4 not work for you?

 

[laundon]

Thanks Rick, that works a treat! Amazing!!!!