Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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
0
smithandandersen
Asked:
smithandandersen
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ron MalmsteadInformation 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:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now