Forum Discussion
Looking to identify and move address info to another column from group of text
Hi Don,
You may find positions of the first and last numbers in the string and extract what is between as an address. But that only works if you have no other numbers within your text.
You may easy google how to find numbers position, here is an example (your string is in A1)
first number position
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))last number position (that is an array formula entered by Ctrl+Shift+Enter)
=MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0))you may use above in MID to have resulting text, also as array formula
=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)Example is attached
Thank you, Sergei! Much appreciate the time to add your thoughts!
Don, that's not a good solution if the solution at all, but that's all what i have in mind.
I modified pefious formulas a bit to avoid array ones:
This one finds position of last digit in post code assuming you have that 5-digits number in the string (and more chances to find compare to last number in the string)
=SEARCH(AGGREGATE(14,6,1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5)),1),A1)+5When the address is extracted by MID as
=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SEARCH(AGGREGATE(14,6,1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5)),1),A1)+5-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))no array formulas. Sample is attached