Function Problems

How to Extract the First Five Characters in Excel (Using Formula)

Bushra Parveen Sep 25, 2026 871 views

Question details

The user needs to extract exactly the first five characters from a specific cell and display them in a different cell using an Excel formula.

How to Extract the First Five Characters in Excel

Product: Excel
Device & OS: not provided
Scenario: Data extraction and text manipulation in spreadsheets.
Observed behavior: A formula is required to automate the extraction of the first five characters from a source text string to a target cell.

Before you start

Identify the cell containing your source data (e.g., cell B2) and decide on the empty target cell where you want the extracted characters to appear.

Solution 1Recommended

Use the LEFT Function to Extract Characters

The LEFT function is the most direct and efficient way to extract a specific number of characters from the beginning (left side) of a text string.

The LEFT function syntax is =LEFT(text, [num_chars]). It reads the text string from left to right and pulls out the exact number of characters you specify.

Select the target cell

Click on the empty cell where you want the extracted characters to appear (for example, click on cell A2).

Enter the LEFT formula

Type =LEFT(B2, 5) into the cell, replacing 'B2' with the cell reference of your actual source data.

Apply and fill down

Press Enter to see the extracted result. To apply this formula to more rows, click the fill handle (the small square at the bottom-right corner of the cell) and drag it down the column.

Use the LEFT Function to Extract Characters

Dynamic Updates: Because this is a formula, if the original text in the source cell changes, your extracted result will update automatically.

Solution 2

Use Flash Fill for Quick Extraction

If you are using Excel 2013 or newer and prefer not to use formulas, Flash Fill can recognize your typing pattern and extract characters automatically.

WPS Spreadsheet Solution

Extract Text Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful text functions including LEFT, RIGHT, and MID, perfectly mirroring Microsoft Excel's capabilities. You can extract and manipulate your data seamlessly for free.

1. Open your document: Launch WPS Office and open your spreadsheet document containing the text.
2. Input the LEFT formula: Click your target cell and type the formula =LEFT(B2, 5).
3. Fill the column: Press Enter to confirm, then double-click the cell's fill handle to instantly apply the extraction to your entire dataset.

100% compatible with Microsoft Excel formulas like LEFT and RIGHT.Fast, lightweight, and capable of processing large datasets without lagging.Free to use with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I extract the last five characters instead of the first?

You can use the RIGHT function instead of the LEFT function. Simply enter =RIGHT(B2, 5) in your target cell, replacing B2 with your specific source cell reference.

What happens if the source cell has fewer than five characters?

If the source cell contains fewer than five characters, the LEFT function will simply return the entire text of that cell without throwing an error.

Can I extract characters from the middle of a text string?

Yes, you can use the MID function for this. For example, the formula =MID(B2, 3, 5) will extract five characters starting from the third character of the text in cell B2.

Why is my LEFT formula returning an error like #NAME?

A #NAME error usually means there is a typo in the function name. Ensure you typed LEFT correctly and that your syntax exactly matches =LEFT(cell_reference, number_of_characters).

Excel & Data Problems