Mar 18, 2014

I have a spreadsheet I work with which contains a formula that needs to take an item ID letter/number combo, substitute the preceeding letter(s) with leading zeros (if the initial numerical characters are fewer than 5) to make the result return a 5-digit number.

The original ID can be in several possible formats (beside each I will post the desired result):

V7684 07684V366 00366V88 00088V60827 60827VS57871 57871VS67 00067

I have the following formula which works like a charm for all except the last example:

=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS","),"V",")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","0"),"V","0"))))

If the first 2 characters are letters ("VS") and there are fewer than 5 numerical characters, the formula results in a 4-digit number, rather than 5. Using the above example, VS67 returns 0067 instead of 00067.

P.S. I just tried

=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS",""),"V","")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","00"),"V","0")))) which only works when there are 3 or fewer numerical characters.

View 6 Replies
View Related