1

I'm trying to produce a formula that can extract a text before that last comma.

for example:

Let's say in Cell A1 the cell has this: "A100, B100, C100, D100"

I want a formula on Cell B1 that returns the value: "C100"

I was able to produce two formulas that don't do this exactly but think a slight adjustment might do the trick.

1st Formula will return the value after the last comma i.e. "D100"

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))

2nd Formula will return everything before the last comma i.e. "A100, B100, C100"

=IFERROR(LEFT(A1,FIND("=",SUBSTITUTE(A1,",","=",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1),"")

Does anyone know what adjustment is needed to get "C100"?

Thanks in advance for your help

M Muaz
  • 89
  • 5

2 Answers2

2

Using FILTERXML()

enter image description here

• Formula used in cell B1

=FILTERXML("<m><b>"&SUBSTITUTE(A1,", ","</b><b>")&"</b></m>","//b[last()-1]")

You can read more on FILTERXML() a thoroughly researched and examples shown by JvdV Sir.

Excel - Extract substring(s) from string using FILTERXML

You can try this as well,

enter image description here

• Formula used in cell C1

=TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",100)),(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*100-99,100))

One more way using of TEXTSPLIT()

enter image description here

=INDEX(TEXTSPLIT(A1,,", "),LEN(A1)-LEN(SUBSTITUTE(A1,",","")))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
2

With ms365:

enter image description here

Formula in B1:

=@TAKE(TEXTSPLIT(A1,,", "),-2)
JvdV
  • 70,606
  • 8
  • 39
  • 70