How to show only first nth characters in a cell string?
In many Excel tasks, you may need to display only the first few characters of text from a cell—for example, pulling the first 3, 5, or any specified number of characters for codes, abbreviations, or shortened identifiers. This is a common requirement when working with imported data, long strings, or when preparing summaries for reports. By isolating the initial segment of cell content, you can quickly create standardized, concise data displays and facilitate easier comparison, sorting, and data management. Here you’ll find several practical solutions for displaying only the first nth characters in a cell, adapted for different user scenarios and needs.
Show only first nth characters with formula
Use VBA macro to extract the first nth characters from cells
Show only first nth characters with formula
This method uses an Excel formula to extract the first n characters from a cell. For demonstration, let’s display the first 3 characters of a cell’s value. You can easily adjust the number to suit your own needs.
Select a blank cell where you want to display only the first 3 characters from another cell, and enter the following formula:
=LEFT(A1,3)After entering the formula, press Enter. To quickly extract from multiple cells, click the cell with the formula, then drag the fill handle down over the other cells you want to process. Each cell will now show just the first 3 characters from the corresponding original cell.
=LEFT(SUBSTITUTE(A1," ",""),3)This formula first removes all spaces, then extracts the first 3 characters from the resulting string. Useful for codes, serial numbers, or when uniform text structure is required.
Use VBA macro to extract the first nth characters from cells
For advanced users who often need to extract the first n characters from numerous cells automatically, a VBA macro offers a flexible and efficient solution. This approach is especially useful when working with large datasets, where manually entering formulas for each row is time-consuming. By running a macro, you can specify any range and customize the number of characters to extract at once.
1. Go to the Developer Tools tab in Excel and click Visual Basic. In the opened Microsoft Visual Basic for Applications window, click Insert > Module, then copy and paste the following code into the Module:
Sub ExtractFirstNCharacters()
Dim rng As Range
Dim WorkRng As Range
Dim n As Integer
Dim cell As Range
Dim result As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to extract characters", xTitleId, WorkRng.Address, Type:=8)
n = Application.InputBox("Enter number of characters to extract", xTitleId, "3", Type:=1)
For Each cell In WorkRng
If Len(cell.Value) >= n Then
result = Left(cell.Value, n)
Else
result = cell.Value
End If
cell.Offset(0, 1).Value = result
Next cell
End Sub2. After entering the code, click the 
Run button to execute the macro. Follow the prompts: first select your data range, then enter your desired character count. The macro will quickly populate the adjacent column with the extracted results.
Tips & Precautions: This VBA method works on the active worksheet’s selection. Ensure there’s a blank column next to your original data to receive the output. If your range includes formulas or non-text cells, the macro will attempt extraction but may result in unexpected output—always confirm results for accuracy. Using On Error Resume Next helps prevent code interruption in case of minor cell errors. For undo, save your work before running macros or consider duplicating your data for safety.
Application Scenarios: VBA automation is ideal for payroll lists, code formatting, product tagging, and any work requiring repeated extraction actions. It’s particularly powerful for batch processing tasks, and can be customized further for non-contiguous ranges or different output patterns.
Pros: Automates large-scale extraction with customizable parameters. Cons: Requires VBA access and basic macro security settings enabled in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in