Any formula for the auto correction of the text and make it proper as per requirement in MS excel-CodePudding

I have a sheet where column 1 is having New House numbers example 1-4-3, however actually i need it as per the following 01-04-003, like wise there are some other cases where 002-01-01 number is there, it should be 02-01-001.

The logic is the first number is only 2 digit number, second one is also the same, where as third one is 3 digit number.

Any formula is there to resolve the errors in the data?

I tryied nothing in this connection.

Formula in B1:

=MAP(A1:A7,LAMBDA(x,TEXTJOIN("-",,TEXT(--TEXTSPLIT(x,"-"),{"00","00","000"}))))

CodePudding user response：

If you don't have access to the newfangled functions like lambda, textjoin and textsplit, you can try this one:

=TEXT(LEFT(A1,FIND("-",A1)-1),"00") & "-" & TEXT(MID(A1,FIND("-",A1,1) 1,FIND("-",A1,FIND("-",A1,1) 1)-FIND("-",A1,1)-1),"00") & "-" & TEXT(MID(A1,FIND("-",A1,FIND("-",A1,1) 1) 1,LEN(A1)),"000")