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)