0

I currently have cell that has the following output from my Google Sheet:

Share Price vs. Fair ValueBelow Fair Value: AAPL ($126.85) is trading above our estimate of fairvalue ($118.02)Significantly Below Fair Value: AAPL is trading above ourestimate of fair value.

From this cell, I want to extract the second number only, so 118.02 (ideally without the $ and the "S"). Currently my formula looks like this:

=iferror(REGEXEXTRACT(DV2;"\((.*)\)([A-Z,a-z,$])"))

As an output I'm getting

$118.02 and in the cell next to it the letter "S".

Not sure why the regex is adding the "S" along with the number : ) Any way to get rid of it in the REGEXEXTRACT formula?

I tried using this https://regex101.com/ but I still seem to be dumbfounded.

2
  • REGEXEXTRACT is not a formula in Excel.
    – Scott Craner
    May 12 at 13:31
  • Sorry, my bad. Thought it was the same in Excel : (
    – Google Sheet
    May 12 at 13:48
0

Try this formula used in Excel Sheet

Edit :

to extract the 2nd value

In B1, enter formula :

=-LOOKUP(1,-RIGHT(LEFT(A1,MATCH(1,-INDEX(MID(A1,ROW($1:$250),1),0))),ROW($1:$250)))

enter image description here

2
  • Thanks, that formula doesn't work for me, unfortunately. Maybe because I'm using Google Sheets. The number could be in € or other currencies, just as a side note. I tried playing around here: regex101.com/r/EP4lI8/1 but can't seem to figure it out on how to get rid of the pesky "S"
    – Google Sheet
    May 12 at 13:47
  • @GoogleSheet, please see my edited post to revise the formula in extract the 2nd value and ignore the $ , € or other currencies sign
    – bosco_yip
    May 12 at 14:22
0

Taken that the cell in question is DV2, you can use the following formula

=REGEXEXTRACT(DV2;".*\$(.+)\)")

If you want to turn the result to an actual number add ^1 at the end of the formula.

=REGEXEXTRACT(DV2;".*\$(.+)\)")^1

If you need to, you can also add the IFERROR

=IFERROR(REGEXEXTRACT(DV2;".*\$(.+)\)")^1)
1
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. May 13 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy