Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add a number infront of a number
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add a number infront of a number
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 "Nikki" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add a number infront of a number
You could use a formula like:
=20000+a1 and drag down Nikki wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add a number infront of a number
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. "Nikki" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add a number infront of a number
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 "Nikki" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add a number infront of a number
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.) Nikki wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic multiple insertion of 0 infront of a tel number? | Excel Discussion (Misc queries) | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions |