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
Sylvie BDirectorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.