Substitute characters with leading zeros in multiple scenarios

[jerrydiaz]

Hi All–

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 07684
• V366 00366
• V88 00088
• V60827 60827
• VS57871 57871
• VS67 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.

I'm sure it's something silly that I'm overlooking but I cannot figure out what. Can anyone offer a suggestion please?

Thanks!
Jerry

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.

[ChemistB]

Here is an Arrayed formula that works

=TEXT(RIGHT(A1,LEN(A1)-MATCH(TRUE, ISNUMBER(MID(A1&"00000",ROW($A$1:$A$6),1)+0),0)+1),"00000")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[RobertMika]

Try:
=IF(ISNUMBER(--MID(A2,2,1)),0&MID(SUBSTITUTE(A2," ",""),2,4),"00"&MID(SUBSTITUTE(A2," ",""),3,3))

[martindwilson]

assuming you only have V or VS

Please Login or Register  to view this content.

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

[jerrydiaz]

Thank you martindwilson!! That works perfectly!! Awesome!

ChemistB and RobertMika I appreciate your efforts as well.

Just for the record though, ChemistB, the formula doesn't replace the "S" character and
RobertMika, the formula clips off the last character when there is a "VS" (i.e., while V3817 does correctly become 03817, VS3817 returns 00381)

[ChemistB]

Here is the spreadsheet with my formula used on your values. It's set up to remove up to 6 leading letters of any type.

[AlKey]

Try this one

=TEXT(REPLACE(A1,1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,""),"00000")

	A	B
1	V7684 07684	7684 07684
2	V366 00366	366 00366
3	V88 00088	88 00088
4	V60827 60827	60827 60827
5	VS57871 57871	57871 57871
6	VS67 00067	67 00067
7	ABSR123	00123