Combing LEFT and RIGHT formula

[NielWill]

Hello All,

I'm new to the world of "advanced" excel functions (i.e. anything more complex than a =SUM() formula) and I'm looking for a formula to combine a RIGHT formula and then perform a LEFT formula on those results (or maybe there's a better solution, possibly MID?) to find a value in the middle of two "-".


Right now my process is to use (formula 1) a RIGHT formula (=RIGHT(A3,LEN(A3)-FIND("-",A3)) and then (formula 2) (=LEFT(B3,FIND("-",B3)-1)) in a different column to find the value in-between two hyphens and would love to figure out a way to combine these formulas into one formula to save a step (example of what I'm trying to achieve attached as an image). Any suggestions on how I might accomplish this? The strings in question will always be between two hyphens, but the number of characters before and after, as well the number of characters in the text I'm trying to extract, will vary.

Bonus points, is there a way to work Vlookup into the formula so that it performs a RIGHT formula, then a LEFT formula, then does a vlookup?

Thanks!

 

[Joe4]

Welcome to the Board!

Here is one way to get the part between hyphens out in one formula:

=TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",100)),100,100))

If you are trying to use this value in a VLOOKUP, just put this formula in the first argument of your VLOOKUP formula (the argument values do not have to hard-coded, they can be formulas too!).

 

[Fluff]

Hi & welcome to MrExcel.
How about

=REPLACE(LEFT(A3,FIND("^",SUBSTITUTE(A3,"-","^",2))-1),1,FIND("-",A3),"")

 

[NielWill]

Perfect, both of these solutions worked! Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.