Oct 31 2022 09:57 AM
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!
Oct 31 2022 11:27 AM - edited Oct 31 2022 11:29 AM
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"),""))