Add a number infront of a number

[Guest]

Hi,

I have a spreadsheet that has one column with a 4 digit number in each line.
I would like to copy the digits in this column to a new column and then add
a 2 infront of each 4 digits.

For example.

Column 1
8456
8764
7875
7864

Column 2
28456
28764
27875
27864

I have 500 lines so would like to know if there is a quick way of doing this.

Thanks

 

[Guest]

There are a couple ways you could do this, depending whether you want a
formula or just data in Column B. A formula would continue to reflect any
changes made to column A later on.

One way, in B1 enter the formula:

=--("2"&A1)

Copy down as needed.
Note that the ("2"&A1) portion returns a text string, and the -- converts
the text string back to a number.

Another way, copy Column A and Paste to Column B.
Then, in any blank cell enter 20000
Copy that cell
Select your data in Column B
From the Edit Menu, select "Paste Special..."
Check the "Values" and "Add" options
Click OK
Delete the 20000 you entered originally

HTH,
Elkar

 

[Dave Peterson]

You could use a formula like:
=20000+a1
and drag down

 

[Guest]

Various options.

Mathematical:

=20000+A1

Joining the required 2 and what you already have in Column A:

=CONCATENATE(2,A1) or
=2&A1

There may be other exotic alternatives.

 

[Guest]

Nikki,

You can use 8456+20000 in column2, since you have fixed "2" as prefix, so
you just need to all column add "2000"

or there is another way to use =CONCATENATE(2,a1)
=CONCATENATE(2,a2)....

same thing
hope it helps

Hank

 

[Dave Peterson]

I'd use the arithmetic technique (=a1+20000).

But if you decide to use the text version, you'll want to be careful if any of
your 4 digit numbers are less than a 1000:

="2"&text(a1,"0000")
or
=--"2"&text(a1,"0000")

The double minuses change the text back to numbers. (One changes it to a
negative number and the other changes it back to a positive number.)