If you have a list of names or text in Excel where spaces are missing between words, manually adding spaces can be tedious. Fortunately, there are some quick and efficient ways to handle this issue, particularly with newer Excel features like Flash Fill or Power Query.
Table of contents
YouTube: Split text by capital letter in Excel.
Using Flash Fill to Add Spaces.
One of the easiest ways to add spaces in Excel is by using Flash Fill. If you have a column of names or words where spaces are missing, and you want to insert them wherever there’s an uppercase letter, Flash Fill can automatically identify the pattern and fill in the spaces for you.
Instead of manually editing each entry, you simply go to the column next to the one with the issue and type how you want the text to appear. For example, if the original text in Column A is “DougUpdegrave,” you would type “Doug Updegrave” in Column B, showing Excel the desired format, as seen in the image below.
Next, go to Data and select Flash Fill, or use the shortcut Ctrl + E. Excel will automatically try to recognize the pattern and apply it to the rest of the column, inserting spaces where necessary. As shown in the image below, Flash Fill quickly identifies the need for spaces based on the capitalization and applies the change throughout the list.
Using Power Query to Split Text by Uppercase Letters.
Another option is to use Power Query (opens in a new window) to handle missing spaces. Power Query is a powerful tool in Excel that can manipulate and transform data. For this scenario, it allows you to split text wherever there’s a transition from a lowercase to an uppercase letter.
As shown below, in the Power Query editor, select the column with the text, go to Transform, and choose Split Column > By Character Transition. Then, select the option to split where it goes from lowercase to uppercase.
This will separate the words as needed.
Tips and Troubleshooting.
- Make sure Flash Fill has correctly identified the pattern before accepting the changes.
- For large datasets or more complex transformations, Power Query may be more reliable.
- Always check how Power Query treats text casing to avoid unintended errors when splitting or transforming text.
- If the Flash Fill feature doesn’t seem to be working, ensure the columns are adjacent and a pattern exists.