0

Hi I have a cell with data such as:

Dropped pin
Near Hughanden Village Hall, Pome Ln, Hughanden, Low Slokey, HT11 3AT
https://goo.gl/maps/dVtKw423RBA9fkGs7

I would like to use REGEXREPLACE (or one of the other REGEX functions) to remove the text before the URL. As you can see newlines are present which has been stumping me a bit.

To further complicate matters the data in the cell is user submitted with no validation and may not have a URL at all; simply a description or some GPS coordinates. In either of those situations, I would like the cell contents to remain unchanged.

Am I asking too much?

1

=REGEXEXTRACT(A1, "http.*")

0

______________________________________________________

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(REGEXEXTRACT(D2:D, 
 "(\d+.\d+,.*)|(http.*)|\((.*)\)"))),,999^99))))

0

  • Thanks that helps a lot. I don't think I'd really taken much notice of that function! However this doesn't help with the second part of the question - what if the data in the cell is eg (51.6647524, -0.7363616) – fearoffours May 2 '19 at 10:53
  • Here's a partial copy i can share docs.google.com/spreadsheets/d/… - I've now enabled editing of it – fearoffours May 2 '19 at 10:57
  • 1
    @fearoffours answer updated – user0 May 2 '19 at 11:08
  • great. What about row 13? – fearoffours May 2 '19 at 11:16
  • @fearoffours row 13 does not have url nor coords – user0 May 2 '19 at 11:17

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.