How do I add a symbol between numbers in a column?

D

Deb

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?
 
A

Alan

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.
 
P

PCLIVE

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
 
S

Steven

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
 
D

Deb

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.
 
P

PCLIVE

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
 
A

Alan

Did anyone see my original post?
Regards,
Alan.

PCLIVE said:
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
Click to expand...
 
S

Sloth

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.
 
Top