PDA

View Full Version : Formula to find last significant value in string



YellowLabPro
09-02-2007, 04:58 AM
Here is the string:
A1 = Application.WorksheetFunction.Substitute(c.Formula, " JKT ", " JACKET ")
I am looking for a formulae answer to extract:
JACKET
I am only able to get:
JACKET ")

Here is my formula:

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))


Is it possible to add another condition to the same formula to remove the space and what follows?

mdmackillop
09-02-2007, 05:23 AM
=LEFT(Lastword,SEARCH(" ",Lastword)-1)
where Lastword is a named formula
=RIGHT(Sheet1!$A1,LEN(Sheet1!$A1)-FIND("*",SUBSTITUTE(Sheet1!$A1," ","*",LEN(Sheet1!$A1)-LEN(SUBSTITUTE(Sheet1!$A1," ",""))-1))):old:

YellowLabPro
09-02-2007, 05:34 AM
Hello Malcolm, Good Morning....
If I follow what I think are your instructions- I copied your first formula into a Named Formula, then copied the second formula into a cell on the sheet.
If I followed your correct instructions, I do not get JACKET, but rather- JACKET "), the same as my current formula.

Correction: I just reversed the process and bingo.... D*&n you are good....

This was a two-fold purpose. I needed this for a current issue, but also studying formulas. I am going to leave this open to see if this can be done in one step in one formula.... Hoping Xld will happen along and see if possible and if can provide a solution.
But this definitely solves the request..... ps- check your email in a minute.

thanks!

mdmackillop
09-02-2007, 05:39 AM
Well you can always substitute the fomula for Lastword, which gives
=LEFT(RIGHT(Sheet1!$A1,LEN(Sheet1!$A1)-FIND("*",SUBSTITUTE(Sheet1!$A1," ","*",LEN(Sheet1!$A1)-LEN(SUBSTITUTE(Sheet1!$A1," ",""))-1))),SEARCH(" ",RIGHT(Sheet1!$A1,LEN(Sheet1!$A1)-FIND("*",SUBSTITUTE(Sheet1!$A1," ","*",LEN(Sheet1!$A1)-LEN(SUBSTITUTE(Sheet1!$A1," ",""))-1))))-1)
but I defy most to comrehend it!