need to count only numbers after underscore symbol in excel-CodePudding

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

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

CodePudding user response：

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

• 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,

• 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

• Formula used in cell D1

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

With One Spill Array Formula.

• Formula used in cell D1

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

CodePudding user response：

I make the assumption that the characters "cm_" are always before the number:

=len(mid(A1,find("cm_",A1,1) 3,30))

The len() counts the number of characters,

mid() gets the numbers occurring after the text defined in find()

Then:

LEFT(A1,FIND("cm_",A1,1) 2)&"\d{"&LEN(MID(A1,FIND("cm_",A1,1) 3,30))&"}"

CodePudding user response：

My output is here in cell D2..

=REPLACE(C2,MATCH(TRUE,ISNUMBER(--MID(C2,SEQUENCE(LEN(C2)),1)),0),LEN(C2),"\d{"&SUMPRODUCT((ISNUMBER(--MID(C2,SEQUENCE(LEN(C2)),1))*1))&"}")

if your excel doesn't support for Sequence function then you can use ROW(INDIRECT("1:"&LEN(C2)) instead..