0

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
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • I also try to do it with TEXT function but not sure how it will work. Yes these are numeric values –  Aug 18 '21 at 10:01

1 Answers1

0

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).

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you very much But I have Excel 2016 which Does not have TextJoin function. But it will be useful to achieve with Excel 365. –  Aug 18 '21 at 10:04
  • Is formula a must then? Otherwise think about PowerQuery or an UDF? – JvdV Aug 18 '21 at 10:08
  • 1
    You can use `TEXTJOIN()` [UDF](https://stackoverflow.com/a/45845229/5514747) from here by Scott Craner. – Harun24hr Aug 18 '21 at 10:25