Is there a free-space character in the data available in the Excel sheet? This intimation might result from faulty data entry specialists, wrong software, or copying from other sources. The sad part is that most people have to remove space before text manually in Excel, but fortunately, you do not need to, as you can read about our best solutions here.
All of the methods are different and provide different experiences depending on the situation, use the needed method.
How do you remove spaces in Excel before texting with a substitute?
Excel is most efficient for numerical data, using its spreadsheet capabilities. That is why you will most often see a lot more columns that contain numerical data rather than text data, but there are some columns containing text data as well. Although Excel is good at working with figures, the largest challenge we can meet is obtaining spaces between figures.
It can happen due to one or more factors, such as inefficient software populating the Excel sheet. In other words, the Substitute formula is the preferred method to get rid of those spaces. Using this formula, one will be able to get rid of all the unwanted spaces that may be between different digits of one column. However, it is important to note that it only works for a specific number of spaces.
As in the sample below, we only used data that have a single space between the numbers. Since it’s a formula, it can work for entire columns simultaneously. However, one must understand that it works best when it comes to the removal of spaces from numbers. If you want to use the Substitute method, here are the steps to follow:
- Point to the first cell in the column and right-click any blank cell in the selection row. Go to the first cell under the header ‘Number of Substitutes’, and before inputting, make the formula look like this =SUBSTITUTE(A1,” “, “). After the typing of the formula is complete, you can click on any other blank field, or press the Enter button on the keyboard to witness the removal of spaces in your number.
- Up to this point, you have only trimmed, or in other words, eliminated the spaces from one value. If you want to delete them, you will need to hold + drag the fill handle across the entire column. To do this automatically, double-clicking on it will perform the same operation.
- You now have these new values with spaces removed in a new column below. If you want to replace the original values with them, select those values by clicking on the Column Alphabets (A, for instance), copying the Alberto values, and then selecting the original Column Alphabet (A again, for instance). You should know that to paste, you only press Control + Shift + V; otherwise, it will type in the wrong value.
How to Remove a Space Before Text in Excel with TRIM?
Now and then, you produce Excel data with trailing or missing spaces in given different words. Removing them manually is tiring since if the data is large, the process will take a long time. In this case, you cannot use the Substitute method if the number of spaces is unequal since that method does not work in such cases. Therefore, the solution you will be choosing is the Trim.
Substitution is also an integrated function into Excel, like the other one we discussed above, the Substitute function. The advantage of using this function is the ability to eliminate spaces from data in the entire column as soon as possible. It does not contemplate the number of different types of spaces in different cells since all the spaces will be valued in one. Additionally, it is a good choice if you have text data, numbers, or alphanumeric data.
The experience of using this method is even easier than the Substitute method since you are not experiencing the spaces in this formula. So, here is how you can implement it in your use cases:
- Go to the Excel file with unwanted spaces between the data set within a cell. You need to select any empty column in the same row with the first data entry and type the following function: “=TRIM(A1)”. The virtue of this function entails implying and stripping all further spaces in the generally reduced quantity of one. Pressing Enter or any mouse click will reveal this formula’s result.
- Beneath the fill handle, drag it down to apply this formula to the other subsequent rows of these columns. Otherwise, you can double-click, set the formula, and apply it only throughout the whole column. It does not matter how many spaces are in every column; you will find one space replacing all the other spaces in it.
- This step is not compulsory, but if the user wishes to replace the original data with the modified data shown below, Shading intensity levels one, two and three must be copied by selecting them and pressing the Control and C buttons. To do this, choose only the original values to be selected and use the keys Control Shift V simultaneously to paste these values. This will copy values only instead of copying the formula which will soon start retrieving rubbish values.
How to Remove Space Before Text in Excel with Find and Replace?
In the previous methods, the editing tools of the computer and a formula were utilised to eliminate the space between some text, for some users that may be a very difficult procedure because for instance they have never put into practice the use of Excel formula or function. If you share the same sentiment, then we have another one that does the trick right here. It looks better when you don’t require any spaces between the text or numbers.
Likewise, it is suitable for fast computing since all you do is press a button and delete all the spacebar characters. You can even apply this method to delete all the spaces at once in the entire spreadsheet. It also enhances the utilization aspects by presenting better GUI usage.
This method works on almost all versions of Excel, including Excel’s online version. The most encouraging news is that the experience you get on these versions is uniform. So here are steps to perform to delete spaces before or between any text in Excel if you are using the Find method.
- Locate the Excel data file that has leading/trailing or multiple party spaces that need to be eliminated. To open the find window, press the Control + F buttons at the same time on your keyboard. There will be a Replace tab. To perform this procedure, you have to press that tab. This method of opening the Replace window works on every version, including the one you download online.
- When loading in the Find field, you only need to type the space character, which must be done using the space button. In the Replace field, on the other hand, the field should be left blank as usual. That way, when you run it, all the space characters that have been found will have been replaced with no spaces.
- Finally, click the Replace All button to find the values that are being replaced in real-time. The Replace window will also show a bar on the right showing the changes it has made.
