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 66 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,",","")))`
• 59,740 points

## How to build a custom column formula in Power query that will retrieve Image and load it to Excel to display that image from the specified column

Make sure ignore privacy is selected in ...READ MORE

## How to scrape the specific text from kworb and extract it as an excel file?

The best practice to scrape tables is ...READ MORE

## 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