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