Forum Discussion
formula for searching a cell that contains a name or address or certain format
I have an extremely messy excel document where I need to sort nearly 4,000 rows by the columns name, address, and grade. Some cells only have a name, some cells contain the name and address, some cel...
You can try this formula in cell B1 for the first task for the data layout of the example:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,1,SEARCH(" ",A1,SEARCH(", ",A1)+2)),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")
You can try this formula in cell C1 for the second task:
=IF(ISNUMBER(SEARCH("Ohio",A1)),"Ohio","")