Finding a closer value and copying it

Admin 1
Viewed 66

I have a google sheet where it will record payment done in paypal. So basically the users will enter address in all way, so to reduce the errors I have all the possible address the user might enter (This is because this is a payment system for residents in my residential area so I have all the user's address in database). So can anyone help me to find a formula it will find the actual address in "USERNAMES" worksheet and only take the house number and street from the actual data. I have attached some images to explain myself better. I want the formula to be in worksheet "Paypal 2". Please guys I need this help.

https://docs.google.com/spreadsheets/d/1bM8l6JefFsPrlJnTWf56wOhnuSjdIwg3hMbY1tN1Zp8/edit#gid=0 - Link to my google sheets

The address entered by users

                        The address entered by users

enter image description here

                The sheet to look up for the actual address

enter image description here

                   Sheet to split the house number and street
google-sheets-formula google-apps-script google-sheets
1 Answers

Try this:

  1. Delete your formulas from 'Payment Configuration'!B3:C3. (This will temporarily remove all results from B3:C.

  2. Place the following formula into cell B3:

    =ARRAYFORMULA(IF(LEN(PaypalData1!C3:C),PROPER(REGEXEXTRACT(PaypalData1!L3:L, "(\d+)[,\s]+([^,]+\d+)")),))

REGEXEXTRACT allows for extracting more than one grouping, as I've done with this formula; so this should produce a uniform result for B3:C.

The RE2 expression reads "Find the first number(s) followed by any number of commas or spaces and extract the number portion as group one; then find any string after that which does not contain a comma and ends in any number of digits and extract that as group two."

user_139032830
Related