How to retrieve the value within string?
in [General]
|
From: Ron Rosenfeld on 19 Apr 2010 15:35 On Mon, 19 Apr 2010 07:43:38 -0400, Ron Rosenfeld <ronrosenfeld(a)nospam.org> wrote: >On Mon, 19 Apr 2010 03:29:01 -0700, Eric <Eric(a)discussions.microsoft.com> >wrote: > >>There is a string in cell A1, such as >>........ .... (3.25%) >>I would like to retrieve the text inside (), which should return 3.25% in >>cell B1. >>Does anyone have any suggestions on how to retrieve the text within string? >>Thanks in advance for any suggestions >>Eric > >Assuming no parenthesis prior to the desired enclosure: > >=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1) > >--ron OH, and if you want to convert the text to a value, as the other respondents seem to be assuming, you can merely precede the above with a double unary: =--MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1) --ron
From: "David Biddulph" groups [at] on 19 Apr 2010 16:15 Note that it needs to be double unary MINUS. Double unary PLUS wouldn't work. -- David Biddulph "Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message news:q2cps5pfhdfb4infard0gqbcb2ugbno610(a)4ax.com... > On Mon, 19 Apr 2010 07:43:38 -0400, Ron Rosenfeld > <ronrosenfeld(a)nospam.org> > wrote: > >>On Mon, 19 Apr 2010 03:29:01 -0700, Eric <Eric(a)discussions.microsoft.com> >>wrote: >> >>>There is a string in cell A1, such as >>>........ .... (3.25%) >>>I would like to retrieve the text inside (), which should return 3.25% in >>>cell B1. >>>Does anyone have any suggestions on how to retrieve the text within >>>string? >>>Thanks in advance for any suggestions >>>Eric >> >>Assuming no parenthesis prior to the desired enclosure: >> >>=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1) >> >>--ron > > OH, and if you want to convert the text to a value, as the other > respondents > seem to be assuming, you can merely precede the above with a double unary: > > =--MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1) > > --ron
From: T. Valko on 19 Apr 2010 18:28 Here's another one... =--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","") Format as Percentage -- Biff Microsoft Excel MVP "Eric" <Eric(a)discussions.microsoft.com> wrote in message news:A8908C56-B581-4661-9942-3F43F6159250(a)microsoft.com... > There is a string in cell A1, such as > ........ .... (3.25%) > I would like to retrieve the text inside (), which should return 3.25% in > cell B1. > Does anyone have any suggestions on how to retrieve the text within > string? > Thanks in advance for any suggestions > Eric
From: Ron Rosenfeld on 19 Apr 2010 21:20 On Mon, 19 Apr 2010 21:15:41 +0100, "David Biddulph" <groups [at] biddulph.org.uk> wrote: >Note that it needs to be double unary MINUS. Double unary PLUS wouldn't >work. >-- As in the example I gave. And I don't believe that Excel ever interprets (or uses) the '+' sign as a unary operator. --ron
From: Ron Rosenfeld on 19 Apr 2010 21:25
On Mon, 19 Apr 2010 18:28:24 -0400, "T. Valko" <biffinpitt(a)comcast.net> wrote: >Here's another one... > >=--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","") > >Format as Percentage I believe that requires that the ")" is the last non-space character --ron |