Thread: Formula help
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
  
Posts: 1,045
Default Formula help
On Mon, 16 Jun 2014 13:55:07 -0700 (PDT), wrote:

I am trying to figure out this formula:

=MID(SUBSTITUTE(Raw_Data!$A$5," ","^",LEN(Raw_Data!$A$5)-LEN(SUBSTITUTE(Raw_Data!$A$5," ",""))),FIND("^",SUBSTITUTE(Raw_Data!$A$5," ","^",LEN(Raw_Data!$A$5)-LEN(SUBSTITUTE(Raw_Data!$A$5," ",""))))+1,256)

I am not clear on use of the carat (^). This extracts a name from amongst text, but seems overly complicated.

MCS

The ^ is used as a unique marker and is placed at the space prior to the last word in the string; hence the function will return the last word in the string.

If you do not need to return trailing spaces, the following is shorter to return the last word in a phrase. The '99' just needs to be some value longer than the longest possible length of the last word.


=TRIM(RIGHT(SUBSTITUTE(TRIM(Raw_Data!$A5)," ",REPT(" ",99)),99))

Reply With QuoteReply With Quote