Forum Discussion
John_Thibeault
Oct 31, 2022Copper Contributor
Formula to add a 0 in front of numbers<10 with multiple decimals
Hi,
I'm trying to create a formula that will turn a cell such as "3.5.12.5 Introduction" into "03.05.12.05" for a Table of Contents solution. The formula I have functions properly before the first decimal, but I'm having trouble altering it to apply to all.
=IF(ISNUMBER(VALUE(LEFT(B5,1))),IF(VALUE(LEFT(B5,FIND(".",B5)-1))>9,LEFT(B5,FIND(" ",B5)-1),"0"&LEFT(B5,FIND(" ",B5)-1)))
Any help would be appreciated. Thanks!
1 Reply
Sort By
- dscheikeyBronze Contributor
Hi John, I have a formula here for Excel 365 or Excel for the Web. Unfortunately, it doesn't work so easily with older versions. I have used TEXTSPLIT().
=TEXTJOIN(".",TRUE,IF(ISNUMBER(TEXTSPLIT(B5,{".";" "})+0),TEXT(TEXTSPLIT(B5,{".";" "}),"00"),""))