Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I add a symbol between numbers in a column?
I have a column of numbers and I need to add a dash between the first two
numbers without having to do it manually (several thousand rows). Is it possible? |
#2
|
|||
|
|||
How do I add a symbol between numbers in a column?
With your data in column A, in another column try
=LEFT(A1,2)&"-"&RIGHT(A1,LEN(A1)-2) Copy and Paste Special, Values the new column to lose the formulas, then cut and paste the new column to overwrite the original, Try this out on a copy of your original worksheet! Regards, Alan. "Deb" wrote in message ... I have a column of numbers and I need to add a dash between the first two numbers without having to do it manually (several thousand rows). Is it possible? |
#3
|
|||
|
|||
How do I add a symbol between numbers in a column?
If there is a space between the first two numbers, you could use a helpler
column and use the following formula. This is with your number in column A. =REPLACE(A1,FIND(" ",A1),1,"-") If there are no spaces and you just want to put a dash between the first and second number (example. 123 would be 1-23), then try: =LEFT(A1,1)&"-"&RIGHT(A1,LEN(A1)-1) Regards, Paul "Deb" wrote in message ... I have a column of numbers and I need to add a dash between the first two numbers without having to do it manually (several thousand rows). Is it possible? |
#4
|
|||
|
|||
How do I add a symbol between numbers in a column?
You could do something like this. Formula in F5 for this example.
=MID(E5,1,1)&"-"&MID(E5,2,100) I used 100 in this formula because I would not think what you are working with would be longer than that. But if it is then just increase the number. If you want to be more technical you could do ths instead of using 100: =MID(E5,1,1)&"-"&MID(E5,2,LEN(E5)-1) Then you can copy it down. Then do a Copy / Paste Special - Value to change the formual to a value. Hope this help. Steven |
#5
|
|||
|
|||
How do I add a symbol between numbers in a column?
Example: I have a column of 5 character numbers like row 1 is 10011, row 2
is 10012, row 3 is 10013, etc. I need to add a dash after the first two characters like row 1 needs to be 10-011, row 2 needs to be 10-012, row 3 needs to be 10-013, etc. So, I need to insert a dash after the first two digits and do this all the way down the column. "Deb" wrote: I have a column of numbers and I need to add a dash between the first two numbers without having to do it manually (several thousand rows). Is it possible? |
#6
|
|||
|
|||
How do I add a symbol between numbers in a column?
Ok,
Use: =LEFT(A1,2)&"-"&RIGHT(A1,LEN(A1)-2) Once again, this assumes that your number is in column A. Use an available column and use this formula in the same row as the number. Then copy down and to the right as needed. Once all of your numbers are converted, you can copy the entire area and paste (paste special-values only) over the original data. Of course you should backup your data first. Regards, Paul "Deb" wrote in message ... Example: I have a column of 5 character numbers like row 1 is 10011, row 2 is 10012, row 3 is 10013, etc. I need to add a dash after the first two characters like row 1 needs to be 10-011, row 2 needs to be 10-012, row 3 needs to be 10-013, etc. So, I need to insert a dash after the first two digits and do this all the way down the column. "Deb" wrote: I have a column of numbers and I need to add a dash between the first two numbers without having to do it manually (several thousand rows). Is it possible? |
#7
|
|||
|
|||
How do I add a symbol between numbers in a column?
Did anyone see my original post?
Regards, Alan. "PCLIVE" wrote in message ... Ok, Use: =LEFT(A1,2)&"-"&RIGHT(A1,LEN(A1)-2) Once again, this assumes that your number is in column A. Use an available column and use this formula in the same row as the number. Then copy down and to the right as needed. Once all of your numbers are converted, you can copy the entire area and paste (paste special-values only) over the original data. Of course you should backup your data first. Regards, Paul "Deb" wrote in message ... Example: I have a column of 5 character numbers like row 1 is 10011, row 2 is 10012, row 3 is 10013, etc. I need to add a dash after the first two characters like row 1 needs to be 10-011, row 2 needs to be 10-012, row 3 needs to be 10-013, etc. So, I need to insert a dash after the first two digits and do this all the way down the column. "Deb" wrote: I have a column of numbers and I need to add a dash between the first two numbers without having to do it manually (several thousand rows). Is it possible? |
#8
|
|||
|
|||
How do I add a symbol between numbers in a column?
If you only care about how your numbers look, then you can use a custom
number format with ##-### as the type. Your cells will still contain 10011, 10012, 10013, etc. but they will be displayed as 10-011, 10-012, 10-013, etc. Otherwise you will have to create a temporary column with the formulas already listed and then copy and paste special (use the edit menu, or right click to see paste special), and select values. This will paste the result of the formulas. You can then delete the temporary column. "Deb" wrote: Example: I have a column of 5 character numbers like row 1 is 10011, row 2 is 10012, row 3 is 10013, etc. I need to add a dash after the first two characters like row 1 needs to be 10-011, row 2 needs to be 10-012, row 3 needs to be 10-013, etc. So, I need to insert a dash after the first two digits and do this all the way down the column. "Deb" wrote: I have a column of numbers and I need to add a dash between the first two numbers without having to do it manually (several thousand rows). Is it possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Consecutive Numbers down a column not to Exceed 49 | Excel Worksheet Functions | |||
column filtering to match identical numbers | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Average of numbers in column between to other numbers | Excel Discussion (Misc queries) |