Formatting Text in cells that already have text entered

[Jennifer V.]

I have approx several thousand cells all with the same info, let's say SSNs.
They are in the cell as 123456789. I need to format all cells in this column
with the format 123-45-6789. Obviously, I'm not using SSNs, but I'm using
characters that I need formatted into abcde-12345-fg. Is there a way to
custom format these so that it applies to all of these cells instead of me
altering each one by hand???

Thanks,

Jennifer

 

[Glenn]

I have approx several thousand cells all with the same info, let's say SSNs.
They are in the cell as 123456789. I need to format all cells in this column
with the format 123-45-6789. Obviously, I'm not using SSNs, but I'm using
characters that I need formatted into abcde-12345-fg. Is there a way to
custom format these so that it applies to all of these cells instead of me
altering each one by hand???

Thanks,

Jennifer

Could you just list a couple examples of how your real data is currently entered
and then show how you want it formatted?

 

[Jennifer V.]

aa1234
bb1234
cc4321
dd0987
mj5830

It needs to have a space or a symbol between the 2 letters and the 4 numbers.

Thanks!

 

[JBeaucaire]

In an adjacent cell, put this formula:

=LEFT(A2,2)&"-"&RIGHT(A2,4)

This assumes their all the same 6-digit length.

 

[JBeaucaire]

One last thing, after copying that formula down to create an entire row,
highlight the whole range, COPY, EDIT>PasteSpecial>Values.

Now you can delete the original data and use the new codes.

 

[jb_tenor1]

A much easier answer to this (for all numbers) would be to simply highlight
all of the data in the column and Format Cells > Custom

Then, in the section that says "Type:" enter the following:

###-##-####

This will take the number and format it just like a SSN. If you're doing
two alphas and then a five or six-digit number. Do the same as above, but
instead enter: