Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Left funtion challenges

0

I need to use a LEFT function to capture text surrounded by parentheses.  The challenge is the text in the initial cell reference is not consistent.  There might be two sets of text surrounded by parentheses.  How can I use the LEN function to my advantage?  Thanks in  advance.

Answer
Discuss

Answers

0
Selected Answer

 Hi FDNYMom and welcome to the Forum

To find text between parentheses, I'd suggest that you forget LEFT and LEN but instead use Excel's functions MID (get mid-string) and FIND (find text within a string).

  • MID takes two arguments- the text to be checked, the start number (for the first letter you want) and number of characters needed after that. There's an optional third argument too- the earliest character you want to search from (which is the first, if omitted)
  • FIND takes two arguments- the text you're looking for and the text to be checked. 

In the attached file, the yellow cell A2 contains the text "A formula (from Teach Excel) works here (to your advantage)." but you can change it.

The formula in cell B2 extracts what's between the first set of parentheses using:

=MID(A2, FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1)

where it takes the mid-string from one after where the first left parenthesis "(" is found and the number of characters is from where the first right parenthesis ")" is found less that first left one. You'll see it produces "Teach Excel".

In C2,I've used the optional third argument for MID so it starts to look for the next left parenthesis after "Teach Excel" (what's in B2). Also, because there may not be a second set of parentheses, the formula is wrapped by an IFERROR function (see the changes in bold):

=IFERROR(MID(A2, FIND("(",A2, FIND(B2,A2))+1, FIND(")",A2)-FIND("(",A2)),"")

so it shows "to your advantage" at present but if there wasn't a second set, it would show nothing. That's because the formula ends with "") so it shows nothing when the FIND creates an error. If you changed that last bold bit to "No second set" for example, than that phrase would appear if there isn't a second set.

Hope this makes sense and helps. If so, please remember to mark this Answer as Selected (that's how the Forum should work!).

Discuss

Discussion

Another reason to absolutely love this site - the fast response.,  Let me try them out in my environement.  

Also--the VBA course is amazxing
FDNYMom (rep: 2) Feb 7, '23 at 12:33 pm
Thanks for selecting my Answer (but you really should try the formulae first, before doing so really!). Thanks for your fast reply too- I've posted four much more involved Answers recently but got no response (and was thinking the email alerts weren't working for some reason. I haven't seen them for months sadly).

If you have a problem, please say so but edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your data.

Glad you like the VBA course- that's all Don's work (so I can take no credit whatsoever). If you want to do similar in VBA, you'd probably use VBA's  functions Instr and InStrRev instead of the worksheet function Find (but how to do that should be a new question).
John_Ru (rep: 6102) Feb 7, '23 at 12:41 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login