How to insert a space every x characters in cell text in Excel?
In Excel, there are times when you need to format text strings by inserting spaces at regular intervals. This can be useful for improving readability, formatting data for specific systems, or preparing content for printing. While Excel doesn’t have a built-in function specifically for this, there are multiple ways to achieve it efficiently. This article will cover different methods and optimizations for inserting spaces after every X characters in Excel cells.
Insert / Add space after every x characters in cells
Insert / Add space after every x characters in cells with a formula
For users with Excel 365 or Excel 2021, a formula-based solution using TEXTJOIN(), MID(), and SEQUENCE() can dynamically add spaces after every X characters.
To insert a space after every n characters (e.g., every 4 characters), use the following. Then, drag the formula down to apply it to other cells.
=TEXTJOIN(" ",TRUE,MID(A2,SEQUENCE(ROUNDUP(LEN(A2)/4,0),,1,4),4))
- "LEN(A2)": Gets the total number of characters in the cell.
- "SEQUENCE(ROUNDUP(LEN(A1)/4,0),,1,4)": Creates a dynamic array with starting positions for every 4 characters.
- "MID(A2, ..., 4)": Extracts segments of 4 characters.
- "TEXTJOIN(" ", TRUE, ...)": Joins these segments with spaces.
Insert / Add space after every x characters in cells with Kutools for Excel
If you frequently format text this way, "Kutools for Excel" provides a user-friendly way to insert spaces after every X characters with just a few clicks. In addition to this, its "Add Text" feature offers even more flexibility—it allows you to insert specific characters not only at fixed intervals but also at the beginning, end, or any custom position within the text.
Moreover, you can set conditions to insert characters before or after specific words or patterns, making it a powerful tool for advanced text formatting and data manipulation. Whether you're adjusting formatting, preparing data for import/export, or enhancing readability, Kutools streamlines the process efficiently.
After installing Kutools for Excel, please do with the following steps:
- Select the text strings that you want to insert space.
- Then click "Kutools" > "Text" > "Add Text", see screenshot:
- In the "Add Text" dialog box, do the following operations:
- Press the space bar once into the "Text" box;
- Then, select the "Specify position" under the "Add position", and type the positions that you want to insert the space;
- Click "OK" button.
Result: Spaces are inserted after every four characters in the selected cells as following screenshot shown:
Insert / Add space after every x characters in cells with User Defined Function
For advanced users or those working with large datasets, "User Defined Function" provides a powerful and automated way to insert spaces efficiently.
- Hold down the "ALT" + "F11" keys to open the "Microsoft Visual Basic for Applications" window.
- Then click "Insert" > "Module", and paste the following code in the "Module" window.
VBA code: Insert space after every x characters:Function InsertSpaces(ByVal txt As String, ByVal n As Integer) As String 'Updateby Extendoffice Dim i As Integer, result As String For i = 1 To Len(txt) Step n result = result & Mid(txt, i, n) & " " Next i InsertSpaces = Trim(result) End Function - Then, enter the following formula into a blank cell, and drag the fill handle to apply this formula to other cells.
=InsertSpaces(A2, 4)
By leveraging these methods and optimization tips, you can efficiently insert spaces after every x characters in Excel, ensuring your data is well-formatted, readable, and ready for further analysis or reporting. Whether you prefer formulas, third-party tools, or VBA, there’s a solution tailored to your needs and skill level. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Related article:
How to add / insert certain character every x characters into cells?
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