Feb 03 2023 12:44 PM
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
Feb 03 2023 01:15 PM
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","")