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
LVL 6
Smith and AndersenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ron MalmsteadConnect With a Mentor Information Services ManagerCommented:
....in this example cell A1 = 111 222 3333 ext 444

=SUBSTITUTE(A1,"ext ","ext 1")

..would give you ... 111 222 3333 ext 1444
0
 
CompProbSolvCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
=REPLACE(A1,LEN(B20)-2,0,"1")
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
dsackerContract ERP Admin/ConsultantCommented:
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. :)
0
 
CompProbSolvCommented:
Please ignore my original post.  I missed where the number was to be inserted.  ssiqibh and xuserx2000 provided appropriate answers.
0
 
Ron MalmsteadInformation Services ManagerCommented:
Since you also mentioned you are using excel to update user data,

Here is an article I wrote a long time ago, ...
http://www.experts-exchange.com/Software/Server_Software/File_Servers/Active_Directory/A_1724-Active-Directory-Bulk-updating-user-information-using-Excel.html
0
 
Saqib Husain, SyedEngineerCommented:
I wonder why the first correct answer was ignored.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.