Search code examples
excelexcel-formulaformula

Striving to Add 0 to complete the string number to 3

I have data set which i want to convert from this. I have tried with custom formatting but its result is different.

=TEXT(A1,"000")

Data
.3.11.333
.3.3.3.3
11.33.1

to this

Result
003.011.333
003.003.003.003
011.033.001
Solution

  • Looking at your data I don't actually think you are dealing with numbers, but text that includes numeric characters. Maybe your best bet is to "split" your data into an array before using TEXT(), and then join them back together:

    enter image description here

    Formula in B1:

    =TEXTJOIN(".",,TEXT(FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[.*0=0]"),"000"))

    The point here is to only include elements from the array that are actually numeric. We filter them through xpath-expression [.*0=0] to avoid the empty string elements from leading or trailing dots.

    EDIT:

    This can be poored into an UDF as such:

    Function REFORMAT(str As String) As String

With Application
    REFORMAT = Join(.Text(.Transpose(.FilterXML("<t><s>" & Replace(str, ".", "</s><s>") & "</s></t>", "//s[.*0=0]")), "000"), ".")
End With

End Function

    Call through =REFORMAT(A1).