Function Problems

Extract Names, Addresses, and States from Messy Excel Text

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 869 views

Question details

Extract names and addresses from text cells, remove leading numbers from names, and identify state names such as Ohio.

Extract Names, Addresses, and States from Messy Excel Text
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.
Before you start

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.

Solution 1Recommended

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.

1
Extract the name

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.

2
Extract the address

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.

3
Find Ohio

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.

4
Apply the formulas to other rows

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 the name and address and search for Ohio
Check state matches: SEARCH is not case-sensitive and matches Ohio anywhere in the cell, including in a name or street. Replace Ohio in the formula to look for another full state name, and verify ambiguous matches.
WPS Spreadsheet formulas

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. 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose the Excel workbook containing your text.
  2. 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. 3. Extract the address: Select C1 and enter the address formula from the solution above to return the text after the given name.
  4. 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. 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.
Extract names and addresses into separate columnsFind state names with a simple SEARCH formulaCompatible with Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for everyday editing
microsoft office alternative - wps office

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.