0

enter image description hereI have values such as below in one single cell E2 in excel. How do I skip the paranthesis, space and the text that is there in the cell and add only the values enclosed within the brackets and show the result in D2? (5) text (5) text (5) text (10) text (10) text (15) text (10) text (10) text (20) text (10) text

I've attached an image to show an idea of how it looks.

Medlin Gracia
  • 43
  • 6

3 Answers3

0
=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1,"text"," ")),"(",""),")",""))," ","")
Ko Nayaki
  • 74
  • 10
  • Can you please add an explanation as to what this does? It's difficult to follow with all the nested brackets. I also think that the cell type needs to be changed from text to value, but I'm not sure if this macro does that. – Ken Y-N Oct 30 '17 at 23:46
  • @KenY-N, "substitute" is used to replaces the old text "text" with new text " ", same is followed for "(" and ")" Below is revised version, =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A9,"text",""),"(",""),")","")," ","") followed by =VALUE(TRIM(A1)) would change the text to number – Ko Nayaki Oct 31 '17 at 00:04
  • I've attached a screenshot above showing how it looks. – Medlin Gracia Nov 02 '17 at 01:17
  • hi @MedlinGracia, we are not able to view the screenshot – Ko Nayaki Nov 02 '17 at 02:32
  • Is the screenshot still not visible? I attached it twice :O – Medlin Gracia Nov 04 '17 at 01:36
0

Another,

=SUMPRODUCT(--REPLACE(A1:A10, FIND(" ", A1:A10), LEN(A1:A10), TEXT(,)))
0

If given input string is well formatted, I can extract the number with excel function.

=value(MID(F1,FIND("(",F1)+1,FIND(")",F1)-FIND("(",F1)-1))

This function is composed of 3 elemental functions.

=value(MID(  F1  ,  FIND("(",F1)+1  ,  FIND(")",F1)-FIND("(",F1)-1  ))
F1: we want to extract the number from this
FIND("(",F1)+1 : where the number start from
FIND(")",F1)-FIND("(",F1)-1 : calculates the length of the number
MID:extract a string
value :deal(cast) a string as a number
naoki fujita
  • 689
  • 1
  • 9
  • 13
  • Sorry, my function returns a string. if we want to calculate with extracted value, we need change a string into a number. Excel's value function can be used for this purpose, I update my answer. – naoki fujita Nov 07 '17 at 10:03