問題描述
找到更接近的值並複制它 (Finding a closer value and copying it)
我有一個谷歌表格,它將記錄在貝寶中完成的付款。所以基本上用戶會以各種方式輸入地址,所以為了減少錯誤我有用戶可能輸入的所有地址(這是因為這是我居住區居民的支付系統,所以我在數據庫中擁有所有用戶的地址)。所以任何人都可以幫我找到一個公式,它將在“用戶名”中找到實際地址。工作表,僅從實際數據中獲取門牌號和街道。我附上了一些圖片來更好地解釋自己。我希望公式出現在工作表“Paypal 2”中。請大家幫忙。
參考解法
方法 1:
Try this:
Delete your formulas from 'Payment Configuration'!B3:C3. (This will temporarily remove all results from B3:C.
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."