You probably know about using FIND to report the location of a spcific character or the start of a string from within another string, for instance:
=FIND("p",A1) will return 2 from the string "Apple peel" in cell A1.
If you wanted to find the location of the third p then FIND is no use in this form. Obviously this isn't the best example, you are more likely to be trying to do this when you have a complex reference, for instance something like a unique reference made up of "First Name"_"Last name"_"grade"_"business unit" and you want to be able to pull out all the information up until "grade". To do this you can't rely on the name fields being the same length so you need to pull out the location of the third underscore.
To find the location of the third instance of "_" use:
=FIND(CHAR(1),SUBSTITUTE(A4,"_",CHAR(1),3))
You can now use this to pull out information up until the string using, for instance LEFT:
=LEFT(A4,FIND(CHAR(1),SUBSTITUTE(A4,"_",CHAR(1),3))-1)
If you're sure that the data will always have three underscores in then this s good enough. However it will return an error if this isn't true. To overcome this you can put in one of two error handles:
Either this one that checks if the cell contains at least 3 underscores;
IF(OR(A4="",LEN(A4)-LEN(SUBSTITUTE(A4,"_",""))<3),"",LEFT(A4,FIND(CHAR(1),SUBSTITUTE(A4,"_",CHAR(1),3))-1))
or this one that just shows blank if there is any sort of error
=IFERROR(LEFT(A4,FIND(CHAR(1),SUBSTITUTE(A4,"_",CHAR(1),3))-1),"")
Obviously if you want the 2nd or 4th just replace the 3 in CHAR(1),3 with your choice
Note that you can use either "_" or CHAR() values which can be useful if the separator is either unusual or can be confused for instance short and long dashes. For a complete list of CHAR() values.
For more blog entries on Excel, MS Project, Planning or SummaryPro please have a look at the rest of our Blog Entries