Hey all,
A particular cell my contain the following text: "1. Multi-zone VAV AHUs (6) - 101, 102, 105, 111, 112, 117; Points: 30 per unit (DWG: P3 M7.1.3.1)"
The result I'm looking for is the only parenthesis that contains a number, therefore in this example would be 6. Hope someone can help out with this. Thanks!
T
Is it always going to be the case that the parenthesis you want will be the first ones? Or will the formula have to assess all (fixed? variable?) parenthesis pairs?
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
The first set of parenthesis may not always include a number. Another example would be "16. Hydronic Systems (CHW, HHW) - North: (5) AHU 104, 105, FCU 101, 102, MUA-1, South: (6) AHU 106, 111, 112, 115, 117, 119 (DWG: P3 M4.1.1)" where the two numbers would be summed up as 11 (5 and 6).
This formula will pull out the text between the first ( and ) encountered in the string:
=MID(A2, SEARCH("(",A2)+1,SEARCH(")",MID(A2, SEARCH("(",A2)+1,100))-1)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi JBeaucaire, could this be modified to also accomodate the example in post #3? https://www.excelforum.com/excel-for...ml#post4856143
are all of your numbers between the parens single digits (like the 5 and 6 in your example)?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Sambo kid, they are strictly numbers but the largest would be triple digits.
so just to clarify the request, there can be anywhere from one (6) to two (6) and (5) (but not 3 or more of these) and they can be anywhere in length of from 1 to 3 digits each like (54) and (321) and you want all other items in parens which can be alpha and alpha numeric ignored and you want the end product to be the sum if there are two or more of these. Is that correct?
Sambo kid, correct. There can be anywhere from one to two values in parenthesis, i.e. (6) and (5) and can be anywhere in length from 1 to 3 digits such as (5) and (111) and all other alphanumeric text within parenthesis will be ignored. A number-only parenthesis would be summed up with any other number-only parenthesis within the same cell.
Here is an example on how to sum values between parenthesis
Enter array formula in B1 and copy down
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Formula:
Please Login or Register to view this content.
This formula will sum up values between multiple parenthesis
Unknown
1. Multi-zone VAV AHUs (6) - 101, (102), (105), 111, (112), (117); Points: 30 per unit (DWG: P3 M7.1.3.1) 16. Hydronic Systems (CHW, HHW) - North: (5) AHU 104, 105, FCU 101, 102, MUA-1, South: (6) AHU 106, 111, 112, 115, 117, 119 (DWG: P3 M4.1.1)
Sheet: Sheet1
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks AlKey and everyone else! That helped out =)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks