I have two cells that need to be merged into one, but the formatting needs to be updated. In one cell there are category IDs and in the other cell are subcategory IDs. None of them are formatted to have leading zeros, which isn't too big of a deal because I can just use the number format for that. The problem is that the subcategories sometimes have a letter at the end. Number format completely ignores these and just leaves them be. Is there a formula/combination of formulas I can use to get data to look like this?
| Category | Subcat | Output |
|---|---|---|
| 1 | 1 | 01-01 |
| 4 | 12 | 04-12 |
| 21 | 1 | 21-01 |
| 21 | 1b | 21-01b |
CodePudding user response：
EDIT
Sorry about the previous answer. This one is correct
=ArrayFormula(IF(A2:A<>"", IF(LEN(A2:A)=1,"0"&A2:A,A2:A)&"-"&IF(REGEXMATCH(B2:B&"","^\d{1}$|^\d{1}\D")=TRUE,"0"&B2:B,B2:B),""))
WRONG (You can use this)
=ArrayFormula(IF(A2:A<>"", IF(LEN(A2:A)=1,"0"&A2:A,A2:A)&"-"&IF(LEN(B2:B)=1,"0"&B2:B,B2:B),""))
CodePudding user response：
Try:
=IF(ISNUMBER(A2), TEXT(A2,"00"), TEXT(LEFT(A2,LEN(A2)-1),"00")&RIGHT(A2))&"-"&IF(ISNUMBER(B2), TEXT(B2,"00"), TEXT(LEFT(B2,LEN(B2)-1),"00")&RIGHT(B2))
And just paste it in every cell in column C
CodePudding user response：
try:
=INDEX(IFNA(TEXT(A1:A, "00-")&
TEXT(REGEXEXTRACT(B1:B&"", "\d "), "00")&
IFERROR(REGEXEXTRACT(B1:B, "\D "))))
