Replacing data in spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several hundred serial numbers that were entered into a spreadsheet
with the wrong format and need them to be changed. They now appear as
123456789 and I need hyphens in them such as 12-345-6789-0.
 
Try this
Create a new column and copy this formula down as far as your data
goes.

=LEFT(A1,2)&"-"&MID(A1,3,3)&"-"&MID(A1,6,4)&"-0"

Then select the whole column and use : copy, paste special, values
to replace the formulae with the text strings it created.
 
Thanks Alex - I'll trr that, but in the meantime, I copied the stuff to Word,
built a macro and was able to then copy and paste back into Excel. A bit
awkward, but was able to get my work done.


Bob
 
Back
Top