SOLVED

Find a return in a text Filed

Copper Contributor

I'm trying to filter out out possible information.

This is how information was entered into one text field.

------

Company name

Address

City StateZip

-------

That is in all one field with returns after Company Name and Address.  State and Zip are right next to each other.

 

I need to separate into 5 different fields.

Looking for ideas how this can be done.

 

I have been using Left,Right and Mid for other information.

But I'm not sure how to look for "returns" especially when there are 2.

Thanks for reading..

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@JeffH13 

=MID(B5,FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)+1,FIND(" ",B5,FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1))-FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)-1)

You can try the attached file with this formula in cell B12. The formulas in range B9:B13 are copied to the right.

address.JPG 

@OliverScheurich 

 

Thank you soo Much!!!

It will save me so much time

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@JeffH13 

=MID(B5,FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)+1,FIND(" ",B5,FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1))-FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)-1)

You can try the attached file with this formula in cell B12. The formulas in range B9:B13 are copied to the right.

address.JPG 

View solution in original post