-1

I have data that looks like: D-8, FE-09, I-18, P-1 in a range.

I would want all the numbers from 1 to 9 to have a 0 before them. The previous sequence would then be

D-08, FE-09, I-18, P-01

Is there any way to do this with formulas or as a last resort with a macro? I can do this with formulas but it's long since I'm doing it step by step; separating the letters from the numbers in 2 columns, adding the leading 0, putting them back together.

Jade
  • 77
  • 1
  • 14
  • [This post](https://stackoverflow.com/questions/7239328/how-to-find-numbers-from-a-string) shows how to find numbers in a string, so it could be adapted to then add `-0` before the number it has found – newuser2967 Feb 18 '20 at 16:45
  • You don't need VBA. This is fairly simple. What have you tried? – Kyle Feb 18 '20 at 16:59

1 Answers1

2

With TEXTJOIN:

=TEXTJOIN(", ",,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,",","</b><b>"),"-","</b><b>")&"</b></a>","//b["&(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*2+1&"]")&"-"&TEXT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,",","</b><b>"),"-","</b><b>")&"</b></a>","//b["&(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*2+2&"]"),"00"))

enter image description here

Per your comments, having only one value in a per cell:

=LEFT(A1,FIND("-",A1))&TEXT(MID(A1,FIND("-",A1)+1,2),"00")

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This works, can we simplify it? There is only one set of letter/number per cell. So D-8 is in one cell, FE-09 in another one, etc. They're all on different rows in one column. – Jade Feb 18 '20 at 18:29
  • @Jade see edit. And next time try to be more clear that the values are all in separate cells. – Scott Craner Feb 18 '20 at 18:36
  • @Scott do you know of a good reference for XPath as used by FilterXML? – chris neilsen Feb 20 '20 at 21:49
  • @chrisneilsen This is what I look at: https://www.w3.org/TR/1999/REC-xpath-19991116/ but it is not always the clearest and as such I have some trial and error on the first pass. – Scott Craner Feb 20 '20 at 21:52
  • @Scott thx, I'm still learning XPath. Given the quality of your answers I'm sure that will be a great start. – chris neilsen Feb 20 '20 at 21:57
  • @ScottCraner Thank you. What if some of the values are as such: LF-34C, I don't want the C at the end to be cleared off? – Jade Feb 24 '20 at 20:13
  • @Jade That is a new question as that was not in the specs for this question. – Scott Craner Feb 24 '20 at 20:20