I've written a simple macro that loops through each cell and replaces the value of the cell with the trimmed value. Go to the Developer tab and select the Visual Basic Button. 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. This trims all the blank space before/after the text string so that the query will return the correct values. With the column that you want to fix selected, just right-click and choose Transform, and Trim. To remove the spaces, Power Query has a Trim feature found in the right-click menu. 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. Like functions and formulas, queries can also be tripped up by the inclusion of a stray blank space. You can then delete the column with the formulas. 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. When you copy the formula down for the whole column, the result looks like this.Īs you can see, the spacing between the words remains in place, but any spacing before or after the text is removed. This will return the result you're looking for: text with no spaces before or after it. So either type the name of the cell or click on the cell that you want to trim and hit Enter. The only thing you need to identify for the TRIM function to work is the text that needs trimming. Let's say we want to remove those spaces for uniformity's sake. The TRIM function removes all spaces in a text string, except for single spaces between words.īelow is an example of a report where some of the cells look like they are indented but the indentation is actually just extra spaces. That scenario leads us to our second method for space hunting. For example, if your cells contain both first and last names, you will probably want to keep the space in between those names. However, there are times when you want to keep spaces between words. Hitting Replace All (keyboard shortcut: Alt + A ) will remove any instances of a space in the data set that you selected.Īlthough this method is really quick and easy, it's only useful for data where you want ALL spaces removed. Make sure there is nothing in the Replace with field. On the Replace tab, place one blank space in the Find what field. That will bring up the Find and Replace window. The keyboard shortcut for this is Ctrl + H. Then open the Find & Select dropdown menu. The first method for space hunting is to use the Find & Replace feature.Īfter highlighting the cells that you want to search through, go to the Home tab. That's because they are not easy to spot, yet can cause frustrating calculation errors. Blank spaces can be a headache for data analysts and formula writers.
0 Comments
Leave a Reply. |