How to split text into separate columns by capital letter in Excel?
When working with data in Excel, you might occasionally encounter cells that contain multiple words joined together, with each word starting with a capital letter, for example, "FirstNameLastName" or "SalesMarchApril". For data cleansing or analysis, you may need to split the content into separate columns at each capital letter, as illustrated in the screenshot below. However, Excel does not offer a direct built-in function to achieve this by capital letter splitting. What practical approaches can help you quickly and accurately separate the words into individual columns based on capital letters?
Below you will find three effective solutions for this problem, each with its own applicable scenario and strengths:
Split text into separate columns by capital letter with formulas
If your data consists of cell values that contain only two words joined by capital letters, Excel formulas can help to split the contents into separate columns. This solution is straightforward, does not require any code or add-ins, and is suitable for smaller datasets or less complex splitting needs.
1. In a blank cell adjacent to your data (for example, cell C2), enter the following array formula to extract the first word from cell A2 (where A2 contains the text to be split):
=LEFT(A2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)After typing the formula, be sure to press Ctrl + Shift + Enter simultaneously. This is required for array formulas in some Excel versions. If done correctly, curly brackets {} will appear around your formula in the formula bar. This formula works by searching for the second capital letter in your cell, then extracting all characters before that position, yielding the first word.
2. To apply the formula to other entries, select cell C2, then drag the fill handle downwards to fill the formula into additional cells in the column. All corresponding first words for each row will be extracted automatically.
3. Next, in the cell to the right (for instance, D2), enter the following formula to extract the remainder of the text (the second word):
=REPLACE(A2,1,LEN(C2),"")This formula removes the first word that was extracted in the previous step, leaving only the second word behind. Simply press Enter to confirm.
4. Similarly, select cell D2 and use the fill handle to drag this formula down to other rows. The text of each cell in your original range will now be split into two columns, separated by the position of the capital letter.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Split text into separate columns by capital letter with VBA code
The above formula-based method is well-suited to cases where only two words need splitting, but cannot handle cells containing more than two concatenated words (e.g., 'FirstNameMiddleNameLastName'). In such cases, you can use VBA code to automatically insert a space before each capital letter, thus segmenting the words. After adding these spaces, you can use Excel's built-in Text to Columns feature to split the text into individual columns.
First, apply VBA code to add spaces before the capital letter to separate the words.
1. Press ALT + F11 to open the Microsoft Visual Basic for Applications editor window.
2. In the VBA editor, click Insert > Module, and paste the following code into the new module window.
VBA code: Insert spaces before each capital letter in a cell
Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
Dim I As Integer
SplitWords = Left(Str, 1)
For I = 2 To Len(Trim(Str))
If (Asc(Mid(Str, I, 1)) > 64) And _
(Asc(Mid(Str, I, 1)) < 91) And _
(Mid(Str, I - 1, 1) <> " ") Then _
SplitWords = SplitWords & " "
SplitWords = SplitWords & Mid(Str, I, 1)
Next
End Function
After typing or pasting the code, close the VBA editor to return to the worksheet. This code defines a new function called splitwords that you can use like a regular worksheet formula. Note that enabling macros may be required to use this function, and you should save your workbook as a .xlsm file to retain the VBA code.
3. In a blank cell next to your data, enter the following formula to insert spaces before every capital letter in cell A2:
=splitwords(A2)This formula calls the user-defined function to process each cell. After pressing Enter, you should see your text with spaces inserted before each capital letter, making individual words distinct.
4. Copy this formula down for all relevant cells using the fill handle, so each row is processed accordingly.
5. At this stage, the cells contain formulas. You may wish to copy and paste the values elsewhere to retain only the processed text, without the formula linkage. To do this, select the processed cells, copy them, then right-click and choose 'Paste Special' > 'Values' in a desired location.
Second, apply the Text to Columns feature to split the cell contents into separate columns by the space.
6. Now, select the cell values containing spaces (the results from the prior step). On the ribbon, go to Data > Text to Columns. When the Convert Text to Columns Wizard appears (Step1), choose the Delimited radio button.
7. Click Next, and in Step2 select the Space option under Delimiters. This will tell Excel to use spaces as the separation point for splitting the data into columns.
8. In Step3, ensure General is selected for the column data format (or choose another format as needed), and select the cell where you want the split data to appear by clicking 
. Make sure the destination range does not overwrite existing data.
9. Click Finish, and Excel will separate the text into multiple columns, each representing a word that was originally separated by a capital letter.
If you encounter an error where some words are not split as expected, check for exceptional situations such as acronyms (multiple capitals in sequence) or trailing spaces. Consider refining your VBA code or double-checking your data for outliers. Also, remember to save your work regularly when using VBA or performing batch data operations.
This method is particularly helpful when dealing with inconsistent text length or words count per cell. However, some users may be less comfortable with running VBA code, in which case you may consider alternative methods below.
Split text into separate columns by capital letter with Kutools for Excel
Kutools for Excel provides a friendly and efficient solution for users of all skill levels to split text by capital letters, particularly useful for large datasets or repetitive tasks. By using its Add Text and Split Cells features in tandem, you can complete the operation with minimal effort and risk.
1. Select the cells containing the original data you want to split.
2. Click Kutools > Text > Add Text. This tool helps you insert a character or separator—such as a space—before each capital letter.
3. In the Add Text dialog box, type a space (or another separator if needed) into the Text input box. Then, under Only add to section, select 1st letter is uppercase. This tells Kutools to add your chosen separator before every capital letter in the selected cells.
4. Click OK. Kutools will automatically add spaces before each capital letter, instantly preparing your data for splitting.
5. Next, with the processed range still selected, go to Kutools > Merge & Split > Split Cells to begin separating the text into columns by delimiters.
6. In the Split Cells dialog, choose Split to Columns under the Type section, and check the Space (or whichever delimiter was added earlier) box to indicate where the splits should occur.
7. Click OK. A prompt will appear allowing you to choose the starting cell for the split results. Specify the destination carefully, ensuring enough space for the split columns.
8. Finally, click OK to complete the split operation. Your data will now be distributed into separate columns at every capital letter, efficiently and accurately.
This method is ideal for complex, large, or frequently updated data, and helps to avoid manual formula adjustments or coding. Remember that Kutools for Excel extends its utility with many other features, facilitating greater efficiency in common and advanced tasks.
Download and free trial Kutools for Excel Now !
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