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

 

Thanks

 

 

1 Reply

@eaugustine2023 

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","")

name.JPG