Phone Formatting

[Guest]

I have a long column of phone numbers that I'd like to put in the same
format; I've tried Find/Replace and Special Formatting to no avail; the
numbers are similiar to this:

419-248-8000
(201) 567-8000
(201)307-4000
(215) 751-5000

I'm a very inexperienced user and not comfortable with Formulas, so consider
that in your response....

Any thoughts??

Jim

 

[Guest]

Your best chance is to make everything inot a number and then custom format
that number. To that end you want to do the following...

Find and replace all of the non numeric character including blank spaces
with nothing. When all of the non-numeric characters are gone your phone
number (which is now a text string) will become a number. Once that is done
then highlight the column and apply the following custom format to it (Format
-> format Cells -> Number -> Custom)
(000) 000-0000

 

[ginetta]

I have a long column of phone numbers that I'd like to put in the same

Hi Jim,

Here is a quick hack that will clean up your phone numbers.
You will need to wrap your head around a couple of functions and you
will work
with 5 columns to get the result e.g. 222-333-4444

Once you get what is going on with the equations you will have lots of
fun.

here goes.

We assume that phone numbers are in column A from row 1 downwards
I will show you Row 1. You can copy down each column to work all
cells.
Column B will count how many characters are in each cell in Column A.

=LEN(A1) (rows below would follow =LEN(A2), =LEN(A3), =LEN(A4) etc)

This is the LEN() function. It counts how many characters are in the
cell A1
(222)-333-4444 would give us a value of 14 as an example

Next in Column C we are going to extract the first 3 numbers. We will
look to see if the first
character is a number, if it is we grab the first 3 characters - if
not we ignore the first character and grab the second third and fourth
characters.
To do this we use LEFT() and RIGHT() to evaluate and grab the
characters.
Basically this formula asks if the first character happens to be a "("
then deduct 1 from the character count we did with LEN() and take
those 3 characters.
(its sloppy I know but then I am a sloppy eater too lol)

=IF(LEFT(A1,1)="(",LEFT(RIGHT(A1,SUM(B1-1)),3),LEFT(A1,3))

Ok, so far we have the first three number of the telephone numbers in
Column C.

Onto Column D.

This is the easy part. We just grab 8 characters starting from the
right. Since all
your numbers have hyphens they would all be the same and therefore we
can
cheat by grabbing the characters backwards from Right to Left like so:

=RIGHT(A1,8)

So now we have Column C with the first three numbers and Column D with
the
back eight numbers.

We just need one more column, Column E to put the numbers back
together again.

=C1&"-"&D1

This now joints the values in Column C with Column D putting a "-" in
between giving you
a nice clean number - 222-333-4444

A fella could combine all of the evaluations of each column and write
them in a single column
but thats another story.

Nick
(e-mail address removed)

 

[Guest]

You don't need 5 columns to get the result 222-3333-4444

Try this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","-")," ","")

copy down as far as needed.

 

[Guest]

Thanks for all of your responses;

I tried Jim's first and had some success....I was able to get rid of all
characters except hyphens, so now my column looks like this....
419-248-8000
201 5678000
201-307-4000
203-750-3000
214 6031000
215 7515000
215633 4212
215-694-8300

I then tried to format the column using (Format
-> format Cells -> Number -> Custom)
(000) 000-0000

What happened is some of the numbers changed and some didn't......I'm left
with:
(419) 248-8000
201 5622543
215633 4212

Any ideas why some numbers would change and others wouldn't??

Thanks!

 

[Pete_UK]

Some of your "numbers" are text values, eg those with a space embedded
within them. Do CTRL-H again on the column, and:

Find what: <a single space>
Replace with: leave blank

If this does not get rid of the space then you probably have a non-
breaking space (character 160) in there. In this case you need to hold
down the Alt key and type 0160 from the numeric keypad where it says
<a single space> above.

Hope this helps.

Pete

 

[Guest]

Thanks Pete....That seemed to solve several problems; However after following
your steps, most of the cells are formatted correctly with the exception of
these:

201-307-4000
214 6031000
215633 4212

I really appreciate all of your help; at this point it seems the time in the
time I've spent looking for a solution, I could have formatted everything
manually!!

Cheers and feel free to keep the solutions coming!

 

[Pete_UK]

You still have non-numeric characters in these and so they will be
treated as text (and the formatting you applied will not work with
them). Assume your first example is in A1 - you can enter this formula
in a nearby cell:

=CODE(MID(A1,4,1))

and it will tell you the character code of the hyphen. The "4" in the
formula is looking at the 4th character in A1, so you can play about
with this for the other offending cells and discover the code for
their unwanted characters. Then you can use these codes as I described
above in conjunction with the Alt key and the numeric keypad and Find
& Replace and see if that gets rid of them.

Hope this helps.

Pete

 

[Ginetta]

Teethless Mama;

Nice Substitution.

I tested it out and except for the instance where there is just a
space
it works a treat.


Nick

 

[Ginetta]

For a single line extraction try:

=IF(ISERROR(LEFT(A1,1)*1),(MID(A1,2,3)&"-"&RIGHT(A1,8)),
(MID(A1,1,3)&"-"&RIGHT(A1,8)))

A little more resilient but still assumes that the last 7 digits are
split with a hyphen

Copy down as needed.

Nick