I'm working on a method to extract text before the final comma.

for instance:

Say the following is in Cell A1: "A100, B100, C100, D100." On Cell B1, I need a formula that outputs the value "C100."

Although I was able to create two formulas that fall short in this particular area, I believe a small modification could work.

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"?

Jan 20 in Others 27 views

## 1 answer to this question.

Using FILTERXML() • 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, • 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() `=INDEX(TEXTSPLIT(A1,,", "),LEN(A1)-LEN(SUBSTITUTE(A1,",","")))`
• 51,240 points

## How to lock data in excel sheet using POI, leaving cells without any data / the rest of the sheet unlocked

If the cells in a row have ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE