Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
Welcome to SuperUser. We all volunteer our time to answer questions here. Asking for an answer ASAP is not constructive to getting a response. –  Brad Patton May 14 '13 at 20:23
    
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 –  Connor Standish May 14 '13 at 20:26
    
Do you know about the 'Text to Columns' feature on the Data tab? –  Brad Patton May 14 '13 at 20:27
    
@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. –  Jo Bedard May 14 '13 at 20:34
add comment

3 Answers

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

share|improve this answer
    
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. –  Brad Patton May 14 '13 at 20:40
    
brad do you have a skype which i could contact you on and give you all the details so you could help me 100%? –  Connor Standish May 14 '13 at 20:42
1  
Sorry no. I do this to volunteer answers for the general public not consult (I get enough of that in my real job :) ). –  Brad Patton May 14 '13 at 20:49
    
Haha i will link a picture and annotate what i mean –  Connor Standish May 14 '13 at 20:50
1  
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). –  Brad Patton May 14 '13 at 21:10
show 1 more comment

Use the convert command on the data tab, choose delimited values, then next and chose custom sign & enter a slash / in the little box down, then next, then chose the text option, next. You should end up with three separate columns. Or if the phone numbers are always the same length you can use a formula. Put your data in column A and adapt the following playing with the second number between parenthsesis to get the desired number of digits:

Column B: right(a1;15)
Colum C:left((right(a1,32)),15)
Col. D:Left(a1, 15) 
share|improve this answer
    
Dont understand what you mean by this? –  Connor Standish May 14 '13 at 20:39
    
@ConnorStandish: I think Jo meant enter a forward slash character, /, as the delimiter character in conjuction with the data tab convert command. –  martineau May 15 '13 at 1:10
add comment

You can use FIND to get the positions of the separators:

' first one
=FIND(" / ",A1)
' 14

' second one
=FIND(" / ",A1,FIND(" / ",A1)+1)
' 30

With this you can split up your text usign LEFT, MID and RIGHT:

=LEFT(A1,FIND(" / ",A1)-1)
' 0121 396 0501
=MID(A1,FIND(" / ",A1)+3,FIND(" / ",A1,FIND(" / ",A1)+1)-FIND(" / ",A1)-3)
' 0121 123 434*
=RIGHT(A1,LEN(A1)-FIND(" / ",A1,FIND(" / ",A1)+1)-2)
' 0797 978 8265

Note the offsets used to take into account of the size of the separator (3 characters).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.