Find a return in a text Filed

New Contributor

I'm trying to filter out out possible information.

This is how information was entered into one text field.


Company name


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)


=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.




Thank you soo Much!!!

It will save me so much time