Add a 9 to phone number

[Guest]

I have a column full of phone numbers, and I need to add a "9" to the front of every number in each cell. Can anyone help please?

 

[Frank Kabel]

Hi Roger

in the adjacent column enter the following formula in row 1
="9" & A1

then copy this formula for all rows
Frank

 

[Norman Harker]

Hi Roger!

Try:

=9&A1

This returns text irrespective of whether your telephone numbers are
text or numeric.

If your telephone numbers are of consistent length you could use
something like

Put 90000000 in a cell
Select
Copy

Select the range of numbers
Edit > Paste Special > Add
OK

This permanently amends your data without the need for an intermediate
helper column stage

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I have a column full of phone numbers, and I need to add a "9" to

the front of every number in each cell. Can anyone help please?

 

[Nikos Yannacopoulos]

Assuming your prone number is in number format in A1:

=9*10^(LEN(A1))+A1

It will return the new phone number still in number format.

HTH,
Nikos

-----Original Message-----
I have a column full of phone numbers, and I need to add

a "9" to the front of every number in each cell. Can
anyone help please?

 

[Peo Sjoblom]

Nice, here's another way for numeric values

=--TEXT("9"&A1,REPT(0,LEN(A1)))

 

[Ken Wright]

Another way of tackling a range of numbers is to put =$A$1 in any cell. Copy
that cell, select the range and do Edit / Paste Special / Add. Every cell gets
tagged with a +($A$1) at the end and an = in front. Edit / Replace = with =9,
and then copy range and paste special as values.

 

[Ken Wright]

LOL - This kind of assumes that A1 is empty though - If not then pick another
one.

 

[David McRitchie]

Hi Roger,
I would recommend that phone numbers always be text,
whether you left or right justify them, you will be able to enter
phone numbers of any country and have the most flexibility.

To change your phone numbers in place you can use a macro.
There are a couple of macros in
http://www.mvps.org/dmcritchie/excel/join.htm

Fix Local Area Phone Codes (#fixphoneareacode)

insertprefix will insert a prefix of your choice in front of TEXT,
which is what you see as opposed to values, or formulas.

If not familiar with macros see my Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm
I do not include this blurb when posted in excel.programming

Be sure to practice usage on a copy of your worksheet, to prevent
loss of data, and to make sure it fits your situation. .