Strip 3 numeric values from string - excel

Question

Ive got the below string in a Google drive spreadsheet in cell D1, what im trying to do is strip out the low the high and the average into cells F1,G1,H1

Currently, we can provide valuations on 3, across a range of prices: from £4,694,700 to £8,091,700 - Average price £6,624,100

At the moment im using =RIGHT(D1, 11) in cell H1 to get the average £6,624,100 number, but im not sure of the best way to get the others, or how to deal with hundreds of thousands, or tens of millions

Is there a more versatile way this could be done ?

Ive made a dummy spreadsheet here : https://docs.google.com/spreadsheets/d/1r_RbMjI-7KLNt8QMyQzn0d-AN1CsOxbVfjYXyoIXXFU/edit?usp=sharing

Ive marked this as an excel question as ive found 99% of the functions that you can use in Excel you can use in Google Spreadsheets (Google spreadsheets cant use excel macros though..)

Scott Craner · Accepted Answer · 2015-12-03 18:12:47Z

4

Sot the three formulas

1st number

=mid(D1,FIND("£",D1,1),find(" ",D1,FIND("£",D1,1))-FIND("£",D1,1))

2nd number

=mid(D1,FIND("£",D1,FIND("£",D1,1)+2),find(" ",D1,FIND("£",D1,FIND("£",D1,1)+2)+2)-FIND("£",D1,FIND("£",D1,1)+2))

3rd Number

=mid(D1,FIND("£",D1,FIND("£",D1,FIND("£",D1,1)+2)+2),999)

answered Dec 3, 2015 at 18:12

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Grade 'Eh' Bacon Over a year ago

Agree that this is likely the best method, assuming that each amount is always preceded by a pound sign and that no other pound signs are present.

sam Over a year ago

Thanks ! - Ive added your formulas into the google spreadsheet and locked it as view only

Grade 'Eh' Bacon Over a year ago

Note that an alternate version of finding each letter would be to use FIND("}}}",SUBSTITUTE(D1,"$","}}}",x)), where x equals the number you're looking for. With 3 searches in this method, I think I still prefer Scott's, but if you had multiple #'s, it would be simpler to use the SUBSTITUTE method.

JPV Over a year ago

Also see my alternative solution using REGEXEXTRACT.

Aurielle Perlmann Over a year ago

@sam give my formula a shot, does this simplify it at all?

JPV · Accepted Answer · 2015-12-03 19:27:25Z

2

Alternatively, in cell E2, try:

=ArrayFormula(regexextract(D2:D4, {"£([0-9,]*)\s", "to £([0-9,]*)\s", "£([0-9,]*)$"} )+0)

and format columns E:G as valuta.

You may want to try this on a duplicate sheet because since this is an arrayformula no values or other formulas should be present in E2:G

answered Dec 3, 2015 at 19:27

JPV

27.4k4 gold badges37 silver badges50 bronze badges

1 Comment

brettdj Over a year ago

Google Spreadsheets has a REGEX function that we have been pestering MSFT to introduce for many years? Remarkable.

Aurielle Perlmann · Accepted Answer · 2015-12-04 05:11:09Z

1

You can use multiple parenthesis to extract separate capture groups, and google sheets automatically splits them into separate cells.

try this:

=REGEXEXTRACT(D1,"(£.*) to (£.*) - Average price (£.*)")

answered Dec 4, 2015 at 5:11

Aurielle Perlmann

5,5691 gold badge19 silver badges28 bronze badges

1 Comment

sam Over a year ago

big time ive moved the accepeted answer

Collectives™ on Stack Overflow

Strip 3 numeric values from string - excel

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related