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. In a blank column, start by typing the equals sign ( =) and the word TRIM, then tab into the TRIM function. 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. BLANK SPACE COPY AND PASTE MOVIEThat sounds like a sci-fi movie title, but it's just someone who can quickly locate and delete superfluous spaces in cell values.īased on my space hunting experience, here are five techniques I use frequently to clean up data. That's because they are not easy to spot, yet can cause frustrating calculation errors.Īfter many years of working with Excel, I've become a Space Hunter. Remove Blank Spaces AFTER.xlsm Space Huntingīlank spaces can be a headache for data analysts and formula writers. BLANK SPACE COPY AND PASTE DOWNLOADYou can download the workbook that I use in the video, including the macro I wrote to remove blank spaces. Watch on YouTube & Subscribe to our Channel Download the Excel File
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |