formula for searching a cell that contains a name or address or certain format

Copper Contributor

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 cells have a name, address, and a bunch of other information. But I want to find a way to extract the names (all are typically in the same format - Last name, First name - within the cell text) or extract the city/state, etc. into new cells, instead of copying/pasting every single cell and moving them around. 


The problem is this information was exported to an excel sheet from a pdf, and the pdf did not convert well. I have cleaned it up for the most part, but I just need to do the final push and avoid copying and pasting 4,000 times.


I am looking for formulas to search a cell for the following:

1. text that is formatted like this "word, word" - and then extract it to a new column (this is pulling a last name, first name but on some of the names there is a string of numbers in front, and I want to eliminate the string of numbers).

2. I want to search the text in a cell for key words, for example, I want to search for "Ohio" in a cell full of text, and then make the next column say "Ohio".





1 Reply


You can try this formula in cell B1 for the first task for the data layout of the example:



You can try this formula in cell C1 for the second task: