[[email protected]]

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

[[email protected]]

MCS:

I'm not an expert, but since I've asked a couple of questions recently in Excel forums I felt obliged to try to answer one.

My take on the formula is that it simply returns the text in cell A5 after the last space. It uses the "^" only as a unique marker within the formula for the position of the last space. My formula is considerably shorter. Hope it works.

Replace each instance of A5 with Raw_Data!$A$5 in the following formula. I used A5 for brevity.

=RIGHT(A5, LEN(A5)-FIND("^", SUBSTITUTE(A5, " ", "^", LEN(A5)-LEN(SUBSTITUTE(A5, " ", ""))), 1))

If by chance the number of spaces in the text is predictable (your formula implies otherwise), then the instance number for the last space is known, and the formula can be greatly simplified.

Best regards,

Greg Wilson

[Ron Rosenfeld[_2_]]

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

[Ron Rosenfeld[_2_]]

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.

[Ron Rosenfeld[_2_]]

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.