ThisCalculator

Everyday

Get Last Line in Cell? Excel Formula Explained

Updated · Standard formula, shown below

This guide explains how to extract the last line of text from a cell in Excel or Google Sheets, giving you the exact formula and the logic behind it. You enter a cell reference, and the formula returns everything after the final line break, which is useful for cleaning up imported data or parsing addresses.

Why You Might Need the Last Line

Cells often contain multiple lines of text, especially when data is pasted from PDFs, emails, or other spreadsheets. Common examples include:

  • Full addresses where the city, state, and ZIP code are on separate lines.
  • Product descriptions with the item name, SKU, and price stacked vertically.
  • Survey responses where each answer was entered on a new line.

If you only need the final piece of that data, manually copying it from every cell is tedious and error-prone. A formula automates the task and updates automatically if the source cell changes.

The Core Formula

In Excel, the line break character is CHAR(10) (LF, line feed). In Google Sheets, it is also CHAR(10) for line breaks created by Ctrl+Enter. The formula to get the last line is:

=TRIM(RIGHT(SUBSTITUTE(A1, CHAR(10), REPT(" ", 100)), 100))

This works by replacing each line break with 100 spaces, then taking the last 100 characters, and finally trimming away the extra spaces. The number 100 is arbitrary but must be larger than the longest line you expect. For a safer version that adapts to any line length, use:

=TRIM(RIGHT(SUBSTITUTE(A1, CHAR(10), REPT(" ", LEN(A1))), LEN(A1)))

Here, LEN(A1) gives the total character count of the cell, so the replacement spaces are always enough.

How the Formula Works Step by Step

Break it down to understand and modify it:

  1. SUBSTITUTE(A1, CHAR(10), REPT(” “, 100)) – Replaces every line break with 100 spaces. This makes the text one long line where each original line is separated by a huge gap.
  2. RIGHT(…, 100) – Takes the last 100 characters from that modified string. Because the last line is followed by 100 spaces (if it was the final line), the last 100 characters will contain the entire last line plus some spaces.
  3. TRIM(…) – Removes leading and trailing spaces, leaving only the clean last line.

If your cell uses carriage returns (CHAR(13)) instead of line feeds, replace CHAR(10) with CHAR(13). Some systems use both (CHAR(10) & CHAR(13)), in which case you can use SUBSTITUTE twice or use a more robust approach with FILTERXML (Excel 365) or SPLIT (Google Sheets).

Alternative Methods for Excel 365 and Google Sheets

Modern versions of Excel (365 and 2021) have dynamic array functions that make this simpler. For Excel:

=TAKE(TEXTSPLIT(A1, CHAR(10)), -1)

TEXTSPLIT splits the text into an array at each line break, and TAKE with -1 returns the last element. In Google Sheets, the equivalent is:

=INDEX(SPLIT(A1, CHAR(10)), COUNTA(SPLIT(A1, CHAR(10))))

Or more elegantly using the QUERY function:

=QUERY(SPLIT(A1, CHAR(10)), "select Col1 offset 1", 0)

These are easier to read and don’t require a magic number like 100. However, they only work if you have the newer software versions.

Common Mistakes and How to Avoid Them

Several pitfalls trip up users:

  • Using the wrong line break character. If your data comes from a Mac or old Windows file, it might use CHAR(13) instead of CHAR(10). Check with the formula =CODE(MID(A1, FIND(CHAR(10), A1), 1)) to see which character is present.
  • Forgetting to use absolute references. When copying the formula down, make sure the cell reference is relative (A1) or absolute ($A$1) as needed.
  • Not accounting for trailing spaces. The TRIM function removes extra spaces, but if your last line intentionally has leading spaces, they will be lost. Use SUBSTITUTE with a different approach if that matters.
  • Using a fixed 100 when lines are longer. If any line exceeds 100 characters, the RIGHT function will cut it off. Always use LEN(A1) as the replacement count to be safe.

When to Use a Formula vs. Text to Columns

If you need to split all lines into separate columns or rows, Excel’s built-in Text to Columns (Data tab) or Flash Fill might be faster. Text to Columns lets you choose “Delimited” and then specify “Other” and press Ctrl+J to enter a line break as the delimiter. This works well for one-time conversions but doesn’t update if the source changes.

Use the formula approach when you need a dynamic solution that recalculates automatically, or when you only want the last line without creating extra columns. For Google Sheets, the SPLIT function with ARRAYFORMULA can also populate multiple cells.

Frequently Asked Questions

How do I get the last line in a cell in Excel without a formula?

You can use Text to Columns with a line break delimiter, or use Flash Fill (Excel 2013+) by typing the last line manually in the first cell and pressing Ctrl+E. Flash Fill recognizes the pattern and fills the rest, but it’s not dynamic.

What if my cell has a line break at the end?

The formula above still works because the trailing line break is replaced with spaces, and TRIM removes them. If you want to avoid the extra spaces, you can first remove the trailing line break with =TRIM(SUBSTITUTE(A1, CHAR(10), ” “)) before applying the last-line formula.

Can I get the last line in a cell in Google Sheets using a simple formula?

Yes, the INDEX/SPLIT method is simple: =INDEX(SPLIT(A1, CHAR(10)), COUNTA(SPLIT(A1, CHAR(10)))). It works in all current versions of Google Sheets.

Why does my formula return #VALUE! or an error?

That usually means the cell contains no line breaks, so SUBSTITUTE finds nothing and RIGHT returns the whole text, which is fine. But if you get #VALUE!, check for non-printable characters or if the cell contains a formula error. Also ensure you’re using CHAR(10) and not a literal “10”.

Is there a way to get the last line that ignores blank lines?

Yes, you can filter out empty lines first. In Excel 365, use =TAKE(FILTER(TEXTSPLIT(A1, CHAR(10)), TEXTSPLIT(A1, CHAR(10))<>“”), -1). In Google Sheets, use =INDEX(FILTER(SPLIT(A1, CHAR(10)), SPLIT(A1, CHAR(10))<>“”), COUNTA(FILTER(SPLIT(A1, CHAR(10)), SPLIT(A1, CHAR(10))<>“”))).

Can I extract the last line from multiple cells at once?

Yes, just drag the formula down. If you’re using Excel 365, you can also use an array formula like =TAKE(TEXTSPLIT(A1:A10, CHAR(10)), -1) to spill results, but it requires consistent line breaks across all cells.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.