Formula help

[machine.control.solutions]

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

 

[nosliwgerg2]

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