What function would you use to remove blank spaces from the beginning of a text string?

10/19/2021

Space Hunting

Blank spaces can be a headache for data analysts and formula writers. That's because they are not easy to spot, yet can cause frustrating calculation errors. 

1. Find & Replace

The first method for space hunting is to use the Find & Replace feature. 

After highlighting the cells that you want to search through, go to the Home tab. Then open the Find & Select dropdown menu. Select the Replace option. The keyboard shortcut for this is Ctrl + H. 

That will bring up the Find and Replace window. On the Replace tab, place one blank space in the Find what field. Make sure there is nothing in the Replace with field. Hitting Replace All (keyboard shortcut: Alt+A) will remove any instances of a space in the data set that you selected. 

Although this method is really quick and easy, it's only useful for data where you want ALL spaces removed. However, there are times when you want to keep spaces between words. For example, if your cells contain both first and last names, you will probably want to keep the space in between those names. 

That scenario leads us to our second method for space hunting. 

2. The TRIM Function

The TRIM function removes all spaces in a text string, except for single spaces between words.

Below is an example of a report where some of the cells look like they are indented but the indentation is actually just extra spaces. Let's say we want to remove those spaces for uniformity's sake. We can use the TRIM function to do that. 

The only thing you need to identify for the TRIM function to work is the text that needs trimming. So either type the name of the cell or click on the cell that you want to trim and hit Enter. This will return the result you're looking for: text with no spaces before or after it. 

When you copy the formula down for the whole column, the result looks like this. 

As you can see, the spacing between the words remains in place, but any spacing before or after the text is removed. 

With this method, we had to create a whole new column, so you would have to add the additional step of replacing the original column with the new one using Copy and Paste Values. You can then delete the column with the formulas.

3. Power Query

Like functions and formulas, queries can also be tripped up by the inclusion of a stray blank space. 

If you are unsure if blank spaces are what's causing the problem, you can click into the Editor's preview to see if there is a space or not.

To remove the spaces, Power Query has a Trim feature found in the right-click menu. With the column that you want to fix selected, just right-click and choose Transform, and Trim. 

This trims all the blank space before/after the text string so that the query will return the correct values. 

4. Macros and VBA

Let's look at the same example we used for the TRIM function, but this time we will use a macro to trim extra spaces from text. 

Start by opening the VB Editor. Go to the Developer tab and select the Visual Basic Button. The keyboard shortcut is Alt + F11. 

I've written a simple macro that loops through each cell and replaces the value of the cell with the trimmed value.

One of the benefits of using a macro is that you can assign it to a button. Here I've assigned it to a button on the actual worksheet. 

And here, I've added it to the ribbon by including this macro in my Personal Macro Workbook and assigning a button to it.

One downside to using the macro method for removing blank spaces is that you cannot undo the action once the macro has been run. In that case, it might be a good idea to save the filebefore running the macro, if you think you might need to un-trim the data for some reason.

It's also important to note that the VBA Trim function does NOT trim extra blank spaces between words. Excel's TRIM function will remove additional spaces between words. So if there are two spaces between the first and last name, TRIM will remove the additional space. VBA's Trim will not do this. It only removes spaces from the beginning or end of the string.

5. Trimming Other Characters

Sometimes after you have trimmed the spaces from your text, you might still get calculation errors. One reason for this could be that another hard-to-detect character might be in play. One such character is the non-breaking space. This is often used in HTML coding ( ) and is similar to a regular space, but would not be deleted by the TRIM function or our macro. 

To determine which character might be gumming up the works, you can use the CODE function. By copying and pasting the invisible character into a blank cell, and then identifying that cell's text in the CODE function (“text” is the only argument that needs to be identified for the CODE function), you will return a code number for that character. A quick Google search of that code number will reveal what type of character you are dealing with.

In this case, the non-breaking space character is code 160.

One way to remove that character from your text would be to use Find & Replace. You can simply copy the character and paste it into the Find what field.

You could also use the REPLACE function to do the same thing. 

One other option, since we know we want to remove the last character in the text, would be to use the LEFT and LEN functions to essentially create a customized trim feature. 

LEFT returns the leftmost characters in a cell, up to a designated amount. LEN (short for Length) returns the number of characters in a cell. 

So combining those two functions and subtracting 1 from the LEN to remove that unwanted character would leave just the text without the additional character. 

Source: excelcampus.com

How do I remove a space at the beginning of a string?

How do I remove spaces from a text string?

How do I remove a space at the beginning of a string in Excel?

How do I remove blank spaces in Excel?