Forum Discussion

JeffH13's avatar
JeffH13
Copper Contributor
Jul 12, 2022
Solved

Find a return in a text Filed

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

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

     

2 Replies

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

     

Resources