Combing LEFT and RIGHT formula

NielWill

New Member
Joined
Jan 18, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Mr Excel Example.jpg
    Mr Excel Example.jpg
    97.6 KB · Views: 30

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Welcome to the Board!

Here is one way to get the part between hyphens out in one formula:
Excel 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!).
 
Upvote 0
Hi & welcome to MrExcel.
How about
Excel Formula:
=REPLACE(LEFT(A3,FIND("^",SUBSTITUTE(A3,"-","^",2))-1),1,FIND("-",A3),"")
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top