Excel extraction filter cell

Question

I want to be able to get only desired data from a cell
For example here is cell A1:

+1.5 (-175) (o/u 8.5) (+118)

I would like to get the value that is in the last parenthesis so new value should be +118

Is the last value always in parentheses? Is the format of all cells (that you want to extract the last value out of) the same? — James L., Apr 29 '19 at 16:41

score 2 · Accepted Answer · answered Apr 29 '19 at 16:45

this will find the last grouping of () and return what is in between regardless of location in the string or number of () pairs:

=LEFT(MID(A1,FIND("}}}",SUBSTITUTE(A1,"(","}}}",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+1,999),FIND(")",MID(A1,FIND("}}}",SUBSTITUTE(A1,"(","}}}",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+1,999))-1)

score 1 · Answer 2 · answered Apr 29 '19 at 16:45

You can create a user defined function like so:

Public Function GetLastNumber(C As Range)
    Data = Split(C.Text, "(")
    GetLastNumber = Replace(Data(UBound(Data)), ")", "")
End Function

Then, in B1 you can type =GetLastNumber(A1) to get +118

If you are unfamiliar with creating a user defined function, check out this answer: How do I extract a series of numbers along with a single letter followed by another series of numbers? that has some pictures to illustrate that.

score 1 · Answer 3 · answered Apr 29 '19 at 16:47

1

If there are always 3 sets of values with parentheses, this formula will pull the value out of the 3rd set.

=MID(A1,FIND("(",A1,FIND("(",A1,FIND("(",A1,1)+1)+1)+1,LEN(A1)-FIND("(",A1,FIND("(",A1,FIND("(",A1,1)+1)+1)-1)

answered Apr 29 '19 at 16:47

James L.

9,384
5
38
77

Excel extraction filter cell

3 Answers3