Function Problems

How to Extract State Abbreviations from Multiline Excel Cells

Chanuka Geekiyanage Oct 1, 2026 869 views

Question details

The user needs to extract two-letter state abbreviations (such as WA, AZ, and NY) from cells containing multiline addresses into a separate column for sorting purposes.

How to Extract State Abbreviations from Multiline Excel Cells

Product: Excel
Device & OS: not provided
Scenario: Organizing and sorting address data where multiple components (business names, addresses, cities, states) are combined in a single cell with line breaks.
Observed behavior: The state abbreviation is located at the end of a multiline cell, requiring a specific formula to isolate it and output it into a new column.

Before you start

Ensure that your data consistently uses line breaks to separate address components, and verify that the state abbreviation is always located on the final line of the cell.

Solution 1Recommended

Use LET and TEXTSPLIT Formulas to Extract the Last Line

Use a dynamic array formula to split the cell contents by line breaks (CHAR(10)) and retrieve the final line containing the state abbreviation.

This formula uses TEXTSPLIT to break the multiline cell into an array of separate text strings based on the line break character. It then uses INDEX and SEQUENCE to reverse the array or extract the final element, ensuring you get the state abbreviation located at the end of the entry.

Select a destination cell

Click on the first empty cell in a new column (for example, B2) where you want the state abbreviation to appear.

Enter the extraction formula

Type or paste the following formula into the cell: =LET(a,TEXTSPLIT(A2,CHAR(10)),INDEX(a,SEQUENCE(1,COUNTA(a),COUNTA(a),-1)))

Apply the formula

Press Enter to execute the formula. The state abbreviation from the last line of cell A2 will now be displayed.

Fill down the column

Click the small green square at the bottom-right corner of cell B2 and drag it down to apply the formula to the rest of your dataset.

Use LET and TEXTSPLIT Formulas to Extract the Last Line

Formula Modification: If some cells have a different structure or do not contain a state at the very end, you will need to adjust the INDEX function to target the specific line number.

WPS Spreadsheet

Easily Extract and Sort Data with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced dynamic array functions like TEXTSPLIT, making it incredibly easy to parse multiline addresses and extract specific data points without hassle.

1. Open your file: Launch WPS Spreadsheet and open the workbook containing your multiline address data.
2. Prepare a new column: Insert or select a new blank column adjacent to your data to store the extracted state abbreviations.
3. Input the formula: Type your text extraction formula (e.g., using TEXTSPLIT and INDEX) into the first cell of the new column.
4. Auto-fill the data: Double-click the fill handle in the bottom right of the active cell to automatically copy the formula down to the remaining rows.
5. Sort by state: Highlight your new column, navigate to the 'Data' tab, and click 'Sort' to instantly organize your records alphabetically by state.

Fully compatible with standard Microsoft Excel formulas and functionsIncludes advanced data sorting and built-in Text-to-Columns utilitiesFree, lightweight, and user-friendly interface for effortless data management

microsoft office alternative - wps office

Frequently Asked Questions

What character code represents a line break in Excel formulas?

In Windows, a line break within a cell is generated using Alt+Enter and is represented by CHAR(10) in formulas. For Mac users, it may sometimes be represented by CHAR(13). Using TEXTSPLIT(A2, CHAR(10)) splits the text at these line breaks.

How can I extract the state if it is not on the last line?

If the state consistently appears on a specific line, such as the third line, you can simplify your formula to =INDEX(TEXTSPLIT(A2, CHAR(10)), 3). This directly targets and pulls the third element from the split text.

Can I use Text to Columns instead of a formula to separate multiline cells?

Yes. Select your data column, go to the Data tab, and click Text to Columns. Choose 'Delimited', check the 'Other' box in the delimiters section, and press Ctrl+J to input a line break character. This will separate each line into its own column.

Excel & Data Problems