asked on

Add single digit in excel

I have a spreadsheet that has all my user info from active directory.
I need to update all users ext numbers in the sheet so I can update active directory.
Currently the phone # in all users accounts is like this:

xxx xxx xxxx ext xxx

I need to be able to add a 1 infront of the ext number..ie: change 323 to 1323

TIA

CompProbSolv

This is not as slick as using VBA, but it will get the job done.

Assume that your phone numbers are in column A and that column F is not used. In F1, put the following:
="1"&a1
Copy that down to the end of data.
Copy column F and paste values to column A.
Delete column F.

Saqib Husain

=REPLACE(A1,LEN(B20)-2,0,"1")

dsacker

You mentioned nothing about VBA, so assuming you are doing this entirely through functions in the spreadsheet itself, you can go out to the right, select an empty column (to use as a work column), then add the following (will assume for instance that your phone number is in column A, starting in row 1 - if otherwise, adjust the formula). Put this in your empty column on row one (for instance, in column Z):

="1 " & A1

Then copy that column all the way down to your last row of data.

Then right-click on the entire column, select Copy, then right-click on column A, then "Paste Special", just the Value.

Voila.

Then erase your column Z. :)

ASKER CERTIFIED SOLUTION

Ron Malmstead

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CompProbSolv

Please ignore my original post. I missed where the number was to be inserted. ssiqibh and xuserx2000 provided appropriate answers.

Ron Malmstead

Since you also mentioned you are using excel to update user data,

Here is an article I wrote a long time ago, ...
https://www.experts-exchange.com/Software/Server_Software/File_Servers/Active_Directory/A_1724-Active-Directory-Bulk-updating-user-information-using-Excel.html

Saqib Husain

I wonder why the first correct answer was ignored.