How do I separate three different phone numbers concatenated in one cell into three new rows?

microsoft-excel microsoft-excel-2010 microsoft-excel-2007 macros worksheet-function

5,850

You can use the Text to Columns feature on the Data tab.

Select that and choose Delimited and then click Next.

On the next screen choose Other and enter the / character. Click Finish

enter image description here

5,850

Connor Standish

Updated on September 18, 2022

Comments

Connor Standish over 1 year
I am doing work for a client and need to know how to separate these phone numbers.

I have
```
0121 396 0501 / 0121 123 434* / 0797 978 8265
```
in one cell and I want to end up with the same row duplicated 3 times, each time with a different phone number from this cell.
- Brad Patton almost 11 years
  
  Welcome to SuperUser. We all volunteer our time to answer questions here. Asking for an answer ASAP is not constructive to getting a response.
- Connor Standish almost 11 years
  
  sorry just the client has asked me to do it now at 10pm and needs this in tomorrow 8am and its a few hundred thousand cells all like this need seperating out
- Brad Patton almost 11 years
  
  Do you know about the 'Text to Columns' feature on the Data tab?
- P. O. almost 11 years
  
  @brad patton He probably doesn't know, that's why is in a state of panic and forgot his manners. The question is definitely not from a "superuser", it's basic Excel stuff. Still, I hope my answer will help.
Connor Standish almost 11 years

Dont understand what you mean by this?
Brad Patton almost 11 years

a) your original post does not include that you may want to go back and edit if 100% needed. b) you could split the data then use Find/Replace or other tools to clean it further.
Connor Standish almost 11 years

brad do you have a skype which i could contact you on and give you all the details so you could help me 100%?
Brad Patton almost 11 years

Sorry no. I do this to volunteer answers for the general public not consult (I get enough of that in my real job :) ).
Connor Standish almost 11 years

Haha i will link a picture and annotate what i mean
Brad Patton almost 11 years

Yeah I think for that many you would be best with a VB macro. You could maybe get by with a combination of Excel tools and VB. The text to columns will separate the data. Use Find and Replace in the cols to clear out the NA. Use VB to expand rows and copy move data. I have to take off now. Best of luck. (And if that is real person data you should delete the image and comment).
martineau almost 11 years

@ConnorStandish: I think Jo meant enter a forward slash character, /, as the delimiter character in conjuction with the data tab convert command.