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 in Others by Kithuzzz
• 27,740 points
27 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 by narikkadan
• 51,240 points

Related Questions In Others

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,420 points
1,102 views
0 votes
1 answer
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 51,240 points
140 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 51,240 points
112 views
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,640 points
205 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,640 points
364 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,640 points
165 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
• 22,940 points
144 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
• 51,240 points
172 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