G
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?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
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
in the adjacent column enter the following formula in row 1
="9" & A1
then copy this formula for all rows
Frank
N
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.
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.
the front of every number in each cell. Can anyone help please?Roger P said:I have a column full of phone numbers, and I need to add a "9" to
N
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
anyone help please?
=9*10^(LEN(A1))+A1
It will return the new phone number still in number format.
HTH,
Nikos
a "9" to the front of every number in each cell. Can-----Original Message-----
I have a column full of phone numbers, and I need to add
anyone help please?
P
Peo Sjoblom
Nice, here's another way for numeric values
=--TEXT("9"&A1,REPT(0,LEN(A1)))
=--TEXT("9"&A1,REPT(0,LEN(A1)))
K
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.
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.
K
Ken Wright
LOL - This kind of assumes that A1 is empty though - If not then pick another
one.
one.
D
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. .
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. .