Search code examples
excelexcel-formulaexcel-2007

need to count only numbers after underscore symbol in excel

I need to check if numbers are there after underscore symbol and put that numbers count as shown below.

aff_ua_affli_key_cm_12548210 aff_ua_affli_key_cm_\d{8} aff_ua_affli_key_cm_125211 aff_ua_affli_key_cm_\d{6} aff_ua_affli_key_cm_1254212 aff_ua_affli_key_cm_\d{7}

I am very new to excel, please how to write a formula for this. Thank you so much in advance

Solution

  • Perhaps you can try something like this. Not sure about your Excel Version.

    enter image description here

    • Formula used in cell B1

    =REPLACE(A1,FIND("@",SUBSTITUTE(A1,"_","@",5)),255,
"_\d{"&LEN(-LOOKUP(0,-RIGHT(A1,ROW($ZY$1:INDEX($Z:$Z,LEN(A1))))))&"}")

    If you have access to MS365 then you could try as below,

    enter image description here

    • Formula used in cell C1

    =LET(x,TEXTSPLIT(A1,"_"),
c,LEN(TAKE(x,,-1)),
TEXTJOIN("_",,DROP(x,,-1),"\d{"&c&"}"))

    I tried made it short in MS365 version

    enter image description here

    • Formula used in cell D1

    =TEXTBEFORE(A1,"_",-1)&"_\d{"&LEN(TAKE(TEXTSPLIT(A1,"_"),,-1))&"}"

    With One Spill Array Formula.

    enter image description here

    • Formula used in cell D1

    =MAP(A1:A3,LAMBDA(m,
TEXTBEFORE(m,"_",-1)&"_\d{"&LEN(TAKE(TEXTSPLIT(m,"_"),,-1))&"}"))