Solved

Add single digit in excel

Posted on 2013-05-23
7
285 Views
Last Modified: 2013-05-23
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
Comment
Question by:smithandandersen
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 21

Expert Comment

by:CompProbSolv
ID: 39191299
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39191308
=REPLACE(A1,LEN(B20)-2,0,"1")
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39191309
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Accepted Solution

by:
Ron Malmstead earned 500 total points
ID: 39191330
....in this example cell A1 = 111 222 3333 ext 444

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

..would give you ... 111 222 3333 ext 1444
0
 
LVL 21

Expert Comment

by:CompProbSolv
ID: 39191351
Please ignore my original post.  I missed where the number was to be inserted.  ssiqibh and xuserx2000 provided appropriate answers.
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39192664
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39193191
I wonder why the first correct answer was ignored.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question