Add single digit in excel

Posted on 2013-05-23
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 change 323 to 1323

Question by:smithandandersen
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 21

Expert Comment

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:
Copy that down to the end of data.
Copy column F and paste values to column A.
Delete column F.
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39191308
LVL 20

Expert Comment

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.


Then erase your column Z. :)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 25

Accepted Solution

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

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

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

Expert Comment

ID: 39191351
Please ignore my original post.  I missed where the number was to be inserted.  ssiqibh and xuserx2000 provided appropriate answers.
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, ...
LVL 43

Expert Comment

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

623 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