0

Let's say I have cells of data that look like this:

Sample text abc (10) and sample text defg (14)

Test 123 abc (14) and more test defg (17)

I would like to extract the numbers in parentheses after abc and the numbers after defg. How would I go about this? I've tried a couple formulas but they get stuck when it encounters a cell with multiple parentheses.

  • How do you want the extracted numbers to be displayed? As a single number like 1014 or separated as 10 14 in a single cell? – Mark Fitzgerald Jan 10 at 0:57
  • Does abc refer to both abc locations in the 2nd example or just the first? On Mark's question, do you want the outputs in separate cells? Are numbers in parentheses only after abc or defg, or can they follow other letter groups? If the latter, do you want the ones only following those two strings? Can a cell contain only one of the strings or none of the strings, or will every cell contain both? Can those strings be in any order? Must the extracted numbers be in the same order as they appear in the cell? Are the numbers always exactly two digits? – fixer1234 Jan 10 at 3:40
  • I would like the numbers extracted in separate cells. So in this example, I'd want to create a column that has what is in parentheses after abc, so 10 and 14, and a different column that has what's in the parentheses after defg, so 14 and 17. I've edited my question as it is unlikely I'll have abc or defg more than once in a cell. The numbers in parentheses will be referring to minutes, so the numbers won't always be exactly 2 digits. – Soum Jan 10 at 16:58
  • It is possible that there will be other numbers in the cell, but I only want to extract the ones after those two strings. The strings may be in any order and the extracted number must be in the same order that they appear in the cell. The logic I was trying to follow in my mind was to have a function that trims everything before the string "abc (" and then everything after the first ")" and likewise for the defg string. – Soum Jan 10 at 17:01
  • Could you provide a sample or screenshot about your problem? – Lee Jan 11 at 1:36
0

Use nested Search() or Find() functions to get to second and third instances of a character.

cell 1 result:

=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)

cell 2 result:

MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,SEARCH(")",A1,SEARCH(")",A1)+1)-SEARCH("(",A1,SEARCH("(",A1)+1)-1)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.