How do I remove the leading 1 from a nine digit telephone #'s stored in a
column in excel. Eg. Get 17035551212 to be 7035551212

To remove the leading 1 from a nine digit telephone number stored in a column in Excel, you can use the following formula:

=RIGHT(A1,10)

Assuming that your telephone numbers are stored in column A, this formula will take the last 10 characters from the right of the cell, effectively removing the leading 1.

Here's how to use this formula:

1. Insert a new column next to the column containing the telephone numbers.
2. In the first cell of the new column, enter the formula =RIGHT(A1,10), assuming that your telephone numbers are stored in column A.
3. Copy the formula down to the rest of the cells in the new column.
4. The new column will now contain the telephone numbers without the leading 1.

Alternatively, you can also use the Find and Replace feature in Excel to remove the leading 1 from your telephone numbers. Here's how:

1. Select the column containing the telephone numbers.
2. Press Ctrl+H to open the Find and Replace dialog box.
3. In the Find what field, enter "1" (without the quotes).
4. Leave the Replace with field blank.
5. Click on the Replace All button.
6. Excel will replace all instances of "1" with a blank space, effectively removing the leading 1 from your telephone numbers.

Hi

one option is to use a "helper" column e.g. if your numbers are in column A,
starting on row 1 in B1 type
=right(A1,len(A1)-1)
(as your phone numbers in the example don't appear to be 9 digits long)
then move the cursor over the bottom right of cell B1 and when you see a +
double click this will fill the formula down the column. When you're happy
with the result select column B and choose copy
click on A1 and choose edit / paste special - values to replace the
information in column A and then delete column B

Hope this helps
Cheers
JulieD


"pc" wrote in message
...
How do I remove the leading 1 from a nine digit telephone #'s stored in a
column in excel. Eg. Get 17035551212 to be 7035551212

Another way:

Put 10000000000 (with commas for easier reading: 10,000,000,000)
in an empty cell.
copy that cell
select your range of 10 digit phone numbers
edit|paste special|select the Subtract option.
Clean up that helper cell.





pc wrote:

How do I remove the leading 1 from a nine digit telephone #'s stored in a
column in excel. Eg. Get 17035551212 to be 7035551212

--

Dave Peterson