On Wed, 18 Jun 2014 09:34:34 -0400, Ron Rosenfeld wrote:

On Wed, 18 Jun 2014 09:23:57 -0400, Ron Rosenfeld wrote:

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))

Actually, your original formula assumed there were no trailing spaces, so, if your data is consistent with that, the TRIM function in the formula I supplied is superfluous, and could be omitted.

That should read that the 2nd TRIM function is superfluous. In other words:

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

should work.