Split Number into Four Separate Parts

Hi,

I am attempting to clean up / modify a large list of twenty-digit ID numbers that should be in the format:

2-digit number (hyphen) 6-digit number (hyphen) 10-digit number (hyphen) 2-digit number – all together, without any space.

For example:

99-123456-1111111111-00

However, a number of IDs appear as:

99-123456-1111111111-

99-123456-111111111-

99-123456-111111111-00

99-123456-111111-twenty

99-123456-1111111111

99-123456-1111111111-00-&22

99-123456-1111111111-22 new

I want to split the different part into four separate columns.

I used the following formulas (which result in a number of errors):

=Left(A1,2)

=Right(Left(A1,9),6)

=Left(Right((A1,13),10)

=Right(A1,2)

Can anyone suggest some better formulas?

Thank you,
Gos-C

Use Data>Text To Columns. On the first screen, select "Delimited", click Next. On the second screen, set the delimiters as Space and in Other, enter a dash (-); click Next. On the third screen, in the Data preview section, note the column headers all say General. Click on each in turn and click "Text" in the Column Data Format box. Use Destination to select your output range (you only need to select the upper left cell of your destination) then click Finish.

Your data is now broken into separate columns.

HTH

Bruce

Thanks, Bruce. Works fine! (Well, the older I get, the more I learn.)

Gos-C

I'm glad it worked for you. Thanks for the feedback, it is always appreciated.

(You should have seen me <then>)



Bruce