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?

Improve this question

1 Answer 1

Reset to default
1 

=REGEXEXTRACT(A1, "http.*")

0

______________________________________________________

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

0

Improve this answer
5
  • 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, 2019 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, 2019 at 10:57
  • 1
    @fearoffours answer updated
    – user0
    May 2, 2019 at 11:08
  • great. What about row 13?
    – fearoffours
    May 2, 2019 at 11:16
  • @fearoffours row 13 does not have url nor coords
    – user0
    May 2, 2019 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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