Extract Names, Addresses, and States from Messy Excel Text
Question details
Extract names and addresses from text cells, remove leading numbers from names, and identify state names such as Ohio.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains unstructured text that combines names, addresses, and states in individual cells.
- Observed behavior
- The desired result is a name and address in separate cells, plus an indicator for a state found in the original text.
Check whether the rows follow a consistent pattern, such as “123 Smith, Jane 456 Main St, Columbus, Ohio,” with a comma and space between the surname and given name. The formulas below assume the address starts after the first given-name word and the source text is in A1.
Extract the name and address and search for Ohio
Use compatible Excel formulas when each row follows the illustrated name-and-address pattern.
The name formula removes every digit from the extracted name, not just leading digits. It stops at the first space after the given-name word, so names with multiple given-name words need a different rule.
In B1, enter =IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,SEARCH(" ",A1,SEARCH(", ",A1)+2)-1),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0","")),""). For the example row, this returns Smith, Jane.
In C1, enter =IFERROR(TRIM(MID(A1,SEARCH(" ",A1,SEARCH(", ",A1)+2)+1,LEN(A1))),""). This returns the remaining text, including the city and state.
In D1, enter =IF(ISNUMBER(SEARCH("Ohio",A1)),"Ohio",""). The formula returns Ohio if that text appears anywhere in A1; otherwise, it returns a blank.
Select B1:D1 and drag the fill handle down through the rows containing source text in column A. Review the results for rows that use a different layout.

Extract names and addresses in WPS Spreadsheet
Open your workbook in WPS Spreadsheet and use the same SEARCH, SUBSTITUTE, and MID formulas to split the illustrated text pattern and identify Ohio.
- 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose the Excel workbook containing your text.
- 2. Extract the name: Select B1 and enter the name formula from the solution above. Adjust the A1 reference if your source text is in a different cell.
- 3. Extract the address: Select C1 and enter the address formula from the solution above to return the text after the given name.
- 4. Identify the state: Select D1 and enter =IF(ISNUMBER(SEARCH("Ohio",A1)),"Ohio",""). Replace Ohio if you need to search for another state name.
- 5. Fill and save: Select B1:D1 and drag the fill handle down. Review the results, then choose File > Save As to save the workbook.

Frequently Asked Questions
Will the name formula remove digits that are part of a name?
Yes. The nested SUBSTITUTE functions remove all digits from the extracted name, not only digits at the beginning. Use a different parsing rule if digits must be preserved.
Why does the extracted name or address appear blank?
The IFERROR formulas return a blank when they cannot find the expected separators. Check that A1 contains a comma followed by a space and a space after the first given-name word.
Can I search for a state other than Ohio?
Yes. In the D1 formula, replace both instances of Ohio with the state name you want. SEARCH is case-insensitive and checks the entire cell.
Does the state formula recognize abbreviations such as OH?
No. Searching for Ohio does not match OH. Searching for OH directly can also match unrelated text, so use consistent delimiters or verify matches before treating them as state abbreviations.




