Replacing data in spreadsheet

S

sgtbob

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

Alex Delamain

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

Bob

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countdown macro - display settings 2
Excel does not accept text format! 0
Word macro that can call cells out of Excel for a find and replace 0
Creating a Chart on form from selected data in query. 0
Linking data in MS Word into Excel 5
Need Help with a VBA subroutine 0
Can't save my Office documents (after computer rebuild and reauthorization) 0
I need to insert a character. 1

Top