I have a spreadsheet that has numbers in the format of:
0001-1234
0258-6847
1658-5487 etc.
Some one has messed up the spreadsheet and now some of the numbers look like:
000112-34
025868-47
165854-87 etc
I need to get the numbers back to their original state. any ideas? some of the things I tried ended up with the number losing the zeros at the beginning.
Are they numbers, or text that looks like numbers?
If they are numbers, change the formatting to 0000-0000.
If they are text tryFormula:
Please Login or Register to view this content.
Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.
Try this in a helper column, copied down...
=LEFT(A1,4)&"-"&MID(SUBSTITUTE(A1,"-",""),5,99)
If you want to, you can then copy/paste values back onto the original data, then delete the helper
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
how would I change the formatting to 0000-0000? I assume that would change a cell from 025868-47 to 0258-6847?
and they are mostly numbers, however, eventually some will be like:
0124-4FDT
If some of them have alphabetical characters in them, not just numerical, use Ford's or my formula and then copy the values back onto the original data as Ford suggested. The formatting option won't work as they are not numbers.
If the number was entered as 2586847 (from your example in post #4) then you could set the number format to 0000-0000 (right click the cell, then on Format Cells, select the Number tab then select Custom and type 0000-0000 in the box under Type: ). The number would then appear as 0258-6847 in the cell, but still be a number that Excel could perform numerical functions on/with. If somebody had changed that formatting to 000000-00 the number would appear as 025868-47. It appears as though your data is not like that though, and the string is entered with the "-" included. Excel therefore treats it as text.
The formula suggested by Gak and myself will take the 1st 4 characters (number or test, doesnt matter), then insert "-" and finally remove any extra "-" and insert what is left.
No formatting needed, it will be treated as text, and it will handle any numeric/text combo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks