Search code examples
excelvbaif-statementexcel-formulaconcatenation

Concatenate specific values from a cell with specific values from another cell into a particular format

enter image description here

          A                       B                                       C
1       numbers                  signs                                **Result**
2    *001*                 *alpha*                       001-alpha
3    *001*111*221*104*     *alpha*kappa*epislon*ETA*     001-alpha, 111-kappa, 221-epislon, 104-ETA
4    *001*085*             *alpha*delta*                 001-alpha, 085-delta

I'm trying to concatenate the values in columns A and B into the following format under the result section. Anything helps, thanks.

Solution

  • Formula solution

    Using Textjoin and Filterxml function, of which Textjoin available in Office 365 or Excel 2019 and Filterxml available in Excel 2013 & later versions of Excel

    In C2, array formula (confirm by pressing Ctrl+Shift+Enter) copied down :

    =TEXTJOIN(", ",1,IFERROR(TEXT(FILTERXML("<a><b>"&SUBSTITUTE(A2,"*","</b><b>")&"</b></a>","//b"),"000")&FILTERXML("<a><b>"&SUBSTITUTE(B2,"*","</b><b>-")&"</b></a>","//b"),""))

    enter image description here