Looking to identify and move address info to another column from group of text

Copper Contributor

Hello,

 

Below is my original post but I think my inquiry is not possible.  Instead, would anyone know how to use a formula to identify a number is a group of text and then execute a text to column for the next 40 or 50 characters?

 

Thanks for your help!

 

Don

 

______________________

Original Post:


I am looking to see if it is possible to use a formula to look a cell with text and numbers and move an address information to the next column? Or, if I can identify what looks like an address and text to column the next 40 or so characters. An example of the text in the column would be as follows:

"The Seattle biotech company lost the race to be first with an approved treatment at 1912 Pike Pl., 
Seattle, WA 98101, using the revolutionary approach called CAR-T cell therapy. But it still aims to be the best in class, says CEO Hans Bishop."

I would ideally like to use a formula to identify "1912 Pike Pl., Seattle, WA 98101" and move it to the next column.

Any help that can be provided is much appreciated.

Thanks!

Don

3 Replies

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)+5

When 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