Extracting part of data

  • Thread starter Thread starter Himani
  • Start date Start date
H

Himani

I have a list of phone numbers in one column and I need to separate
the bit before the space from the bit after it. e.g. 0 0 01444 831918
etc So I need to extract the 01444 before the space into column
Telephone area code and 831918 into telephone. Can someone please
advise? Thanks Himani
 
If your phone numbers are in column A then column B gets the code with
=LEFT(A1,FIND(" ",A1)-1)
Column C gets the phone number with
=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))
 
If all your entries are the same length and follow the same format of
spaces, 5 characters in the area code and 6 characters in the numbers,
you can use these formulas:
=MID(A1,5,5)
=RIGHT(A1,6)

Dave O
Eschew obfuscation
 
What did that do with the OP's example of 0 0 01444 831918, Dennis?

If there is always the same structure of segments, the easiest option may be
Data/ Text to columns/ Delimited/ by space.
 
If your phone numbers are in column A then column B gets the code with
=LEFT(A1,FIND(" ",A1)-1)
Column C gets the phone number with
=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))





- Show quoted text -
Click to expand...

Thank you very much. It works perfectly. Would it be possible to
explain the syntax at all?

Regards

Himani
 
Back
Top