Everything after Last 'X' Char

[roland99]

Hi I need to grab everything after the last "-" (strings can have up to four "-") and if there is a space after the last "-" to not take the space.
Please note that this formula is to work in SharePoint so some formulas may not work

Examples:
TEXT -(THIS VALUE)
TEXT - TEXT -(SPACE)(THIS VALUE)
TEXT - TEXT - TEXT -(THIS VALUE)

 

[mikerickson]

=trim(right(substitute(a1,"-",rept(" ",255),255))

 

[roland99]

Works great except how to I change it so that it grabs the String after the last "-" and not just the last Char?
Currently it returns just ")" I need it to return "(THIS VALUE)"

 

[Rick Rothstein]

Works great except how to I change it so that it grabs the String after the last "-" and not just the last Char?
Currently it returns just ")" I need it to return "(THIS VALUE)"

Mike had a typo... he had missed a closing parenthesis.

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))

 

[mikerickson]

Thanks, Rick.

 

[roland99]

Mike had a typo... he had missed a closing parenthesis.

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))

Ah thanks.

Works great.

 

[roland99]

=trim(right(substitute(a1,"-",rept(" ",255),255))

Sorry to be a pain but looks like substitute is one of the formulas that don't work in SharePoint.
Anyway to use Replace/other formula?

 

[mikerickson]

I'm not familiar with SharePoint.

SUBSITUTE(aString,findString,replaceString)

will replace every instance of findString in aString with replaceString.

Replace sounds like it might be SharePoint's equivalent of SUBSITUTE
(Exel's REPLACE is very different than SUBSTITUTE)

Also, REPT(" ",255) returns 255 spaces, I don't know if REPT has an equivalent in SharePoint.

 

[roland99]

I'm not familiar with SharePoint.


Replace sounds like it might be SharePoint's equivalent of SUBSITUTE
(Exel's REPLACE is very different than SUBSTITUTE)

Replace() should work the same as it does in excel: REPLACE(old_text,start_num,num_chars,new_text)
Also its my understanding that Substitute essentially combines Search and Replace in a loop to find all instances of a string.

I came up with this:
=TRIM(REPLACE(TRIM((RIGHT(A1,FIND(" -",A1)-1))),1,0,""))

It not perfect as if there is no space after last "-" then it keeps the - as part of the string.