How to extract text before the last in excel formula

0 votes

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, 2023 in Others by Kithuzzz
• 38,010 points
359 views

1 answer to this question.

0 votes

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,",","")))
answered Jan 20, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

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

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

answered Feb 18, 2023 in Others by narikkadan
• 63,720 points
536 views
0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,440 points
1,613 views
0 votes
1 answer
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,420 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,674 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
816 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,977 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,720 points
876 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP