breaking up a phone number

  • Thread starter Thread starter maximus73
  • Start date Start date
M

maximus73

I need to break up a phone number into three different columns. I have
column A that contains the phone number xxx-xxx-xxxx, I need to split
that up into three different columns. Column B would equal the area
code... column C would equal the Prefix.. and column D would equal the
digits..

any suggestions on how to accomplish this?

thanks
 
Use the Left, Mid and right functions

If your phone number is in A2 then in B2 put:

=LEFT(A2, 3)

in C2 put:

=MID(A2, 5, 3)

in D2 put:

=RIGHT(A2, 4)

you can then copy B2 to D2 and fill down

HTH
 
Hi

Try this with the number in A1

In B1
=MID(A1,1,3)

In C1
=MID(A1,5,3)

In D1
=MID(A1,9,4)

Select B1:D1 and copy down
 
Hi,
Select the entire data in column A and do the following:
Data --> Text to Columns --> make sure 'Delimited' is checked and click
'Next' --> select the 'Other' button and enter a hyphen mark in the box by
its side and click 'Next' --> 'Finish'.
Regards,
B.R. Ramachandran
 
Another option (for other readers of this thread) is to use the Text to
Columns feature. Insert three blank columns (if needed) next to your
phone number column to receive the data. Highlight the range of phone
numbers and click Data>Text to Columns... Choose "Delimited" and
click Next> Set the delimiter as "Other" and enter the dash "-" and
click Finish.

Now all of your phone numbers are separated into three columns. At
this point, you could (if desired) delete Column A.

HTH

Bruce
 
Hi
We can do in different ways

column b = left(A,3)
column c = mid(A,5,3)
column d = right(A,4)
 
Back
Top