Tough question

[SHAETY]

This may not be able to be done, but it doesn't hurt to ask.

I am copying phone numbers from a web site and pasting onto my
spreadsheet. When I copy the number it looks like 1(216) 555-4847. My
spreadsheet is set up so that the 1 goes in column B, the area code
minus ( ) goes in column C and the phone number minus the space and
dash go in column D.
Is there any type of formula that will allow me to paste into column B
and the area code and phone number are automatically moved into C & D?
I am not worried about deleting ( ), the space and dash. I can always
do a "Find and Replace". I know that I am streaching it but this would
save me soooo much time.
Hope there is a way.
Thanks,
Shaety

 

[Steve Yandl]

If the format is always the same (all numbers have the area code, always
have the 1 in front and the space after the area code, you could try this.

If your phone number is pasted into B1, in C1 you could have
=MID(B1,3,3)
and then in D1 you could have
=MID(B1,8,3)&MID(B1,12,4)

You would drag the fill handles on the two entered formula far enough to
handle all the rows where you've pasted numbers in column B. If the phone
number format sometimes varies, you will need to expand the formulas with
some IF functions.

Steve

 

[Guest]

In B1 thru D1 put:

=LEFT(A1,1) =MID(A1,3,3) =RIGHT(A1,4)

 

[George]

If the positions of the numbers are fixed you can use the text functions
left, mid, right
B1 = LEFT(A1,1)
C1 = MID(A1,3,3)
D1 = SUBSTITUTE(RIGHT(A1,8),"-","")

This can be a little tricky if for example your country code
varies from 1 digit to 2 digits. Then you need something like this

B1 = LEFT(A1,FIND("(",A1)-1)
C1 = MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
D1 = SUBSTITUTE(RIGHT(A1,8),"-","")

Copy the formulas down then...
Once your done copy and paste special > values over themselves

George

 

[kbkst via OfficeKB.com]

Gary's Student:
Thanks for you response however, I am actually starting in B2 thru D2. Does
this change the formula at all?
kbkst

In B1 thru D1 put:

=LEFT(A1,1) =MID(A1,3,3) =RIGHT(A1,4)

This may not be able to be done, but it doesn't hurt to ask.

[quoted text clipped - 11 lines]

Thanks,
Shaety

 

[kbkst via OfficeKB.com]

Steve Yandl:
Thank you so much for your response but I am coming up with a #REF. My
spreadsheet is actually B2, C2 and D2. I don't know if the formual has to
change for this. I tried putting in the B2 within the formula but it is not
working. Hope to hear from you.
kbkst

If the format is always the same (all numbers have the area code, always
have the 1 in front and the space after the area code, you could try this.

If your phone number is pasted into B1, in C1 you could have
=MID(B1,3,3)
and then in D1 you could have
=MID(B1,8,3)&MID(B1,12,4)

You would drag the fill handles on the two entered formula far enough to
handle all the rows where you've pasted numbers in column B. If the phone
number format sometimes varies, you will need to expand the formulas with
some IF functions.

Steve

This may not be able to be done, but it doesn't hurt to ask.

[quoted text clipped - 11 lines]

Thanks,
Shaety

 

[kbkst via OfficeKB.com]

George:
I tried this but not working. I am actually starting with B2, C2, D2.
Please let me know what columns to post these in. I am not very up on the
excel spreadsheet and could really use your help.
Thanks!

If the positions of the numbers are fixed you can use the text functions
left, mid, right
B1 = LEFT(A1,1)
C1 = MID(A1,3,3)
D1 = SUBSTITUTE(RIGHT(A1,8),"-","")

This can be a little tricky if for example your country code
varies from 1 digit to 2 digits. Then you need something like this

B1 = LEFT(A1,FIND("(",A1)-1)
C1 = MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
D1 = SUBSTITUTE(RIGHT(A1,8),"-","")

Copy the formulas down then...
Once your done copy and paste special > values over themselves

George

This may not be able to be done, but it doesn't hurt to ask.

[quoted text clipped - 11 lines]

Thanks,
Shaety

 

[Steve Yandl]

In every instance that you find B1 in my formula, change it to B2. Create
the formulae in C2 and D2 and drag them down.

Steve

Steve Yandl:
Thank you so much for your response but I am coming up with a #REF. My
spreadsheet is actually B2, C2 and D2. I don't know if the formual has to
change for this. I tried putting in the B2 within the formula but it is
not
working. Hope to hear from you.
kbkst

If the format is always the same (all numbers have the area code, always
have the 1 in front and the space after the area code, you could try this.

If your phone number is pasted into B1, in C1 you could have
=MID(B1,3,3)
and then in D1 you could have
=MID(B1,8,3)&MID(B1,12,4)

You would drag the fill handles on the two entered formula far enough to
handle all the rows where you've pasted numbers in column B. If the phone
number format sometimes varies, you will need to expand the formulas with
some IF functions.

Steve

This may not be able to be done, but it doesn't hurt to ask.

[quoted text clipped - 11 lines]

Thanks,
Shaety

 

[Ron Rosenfeld]

This may not be able to be done, but it doesn't hurt to ask.

I am copying phone numbers from a web site and pasting onto my
spreadsheet. When I copy the number it looks like 1(216) 555-4847. My
spreadsheet is set up so that the 1 goes in column B, the area code
minus ( ) goes in column C and the phone number minus the space and
dash go in column D.
Is there any type of formula that will allow me to paste into column B
and the area code and phone number are automatically moved into C & D?
I am not worried about deleting ( ), the space and dash. I can always
do a "Find and Replace". I know that I am streaching it but this would
save me soooo much time.
Hope there is a way.
Thanks,
Shaety

Assumptions:

A. You paste your phone numbers into B2:Bn
B. There is a label in B1
C. The phone number format always has a three digit number for the area code;
a three digit number for the exchange and a four digit number.
D. There is always a non-digit between the 1 (if present), the area code, the
three digit exchange, and the four digit number.

If the above is not always met, decide what other variations could be present.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. <alt-F11> opens the VB Editor
3. Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

4. Paste your list of numbers into B2:Bn

5. <alt-F8> opens the macro dialog box. Select PhoneNums and Run.

6. Enjoy

=============================
Option Explicit

Sub PhoneNums()
Dim c As Range
Dim p As Range
Dim pn As String, temp As String
Dim i As Long

Set p = [b2].CurrentRegion
Set p = p.Offset(1, 0).Resize(p.Rows.Count - 1, 1)

For Each c In p
With c
pn = .Text
.Value = Run([REgex.Mid], pn, "^\d(?=\D)")
.Offset(0, 1).Value = Run([REgex.Mid], pn, "\d{3}")
.Offset(0, 2).Value = Run([REgex.Mid], pn, "\d{3}", 2) _
& Run([REgex.Mid], pn, "\d{4}")
End With
Next c

End Sub
========================


--ron