Get To Know How to Remove Space Before Text in Excel in 3 Effective Ways

Do you have extra spaces in the data present in the Excel spreadsheet? It could be because of inefficient data entry specialists, bad software, or pasting content from different sources. Luckily you don’t need to remove space before text in Excel manually since we have the best solutions that you can try.

Each method offers a different experience in different scenarios, so select the right one for your requirements.

Part 1. How to Remove Spaces in Excel Before Text with Substitute?

The spreadsheet capabilities of Excel are most effective when used for numerical data. That’s why you will most commonly find spreadsheets filled with more columns of numerical data instead of text data, but the text data is also present in some columns. While Excel is very good at dealing with numbers, the biggest problem we can face is getting spaces between numbers.

It can happen for multiple reasons, like software inefficiency that populates the Excel spreadsheet. So, the Substitute formula is the first choice for removing those spaces. This formula will remove all the extra spaces between different digits of one column. However, it is important to note that it only works for a specific number of spaces.

In the example below, we only used data with numbers separated by one space. Since it is a formula, it can easily work for entire columns instantly, but it is important to note that it is most effective for removing spaces from numbers. If you want to use the Substitute method, here are the steps to follow:

• Go to the first value in the column and select any empty cell in the same row. Click on that cell and start typing the formula “=SUBSTITUTE(A1," ", "")." Once you are done typing the formula, you can click on any other empty cell or hit the Enter key on the keyboard to see the spaces removed from your number.

• Till now, you have only removed the spaces from one value. To remove them from the entire column, you can hold + drag the fill handle. For an easier experience, double-clicking it will do the same job for you.

• Now you have the updated values with spaces removed in a new column. If you want to replace the original values with them, select those values by clicking on the column alphabet, pressing Control + C, and then clicking on the alphabet of the original column. Remember that you must press Control + Shift + V to paste; otherwise, it will give a garbage value.

Part 2. How to Remove a Space Before Text in Excel with TRIM?

Sometimes you get Excel data with irregular spaces before or between different words. Removing them manually will be fatigue since if the data is huge, the process will take very long. In this case, you cannot use the Substitute method if the number of spaces is not equal since that method does not work for such cases. So, the solution you will be opting for is the Trim function.

It is also a function that comes built-in into Excel, just like the Substitute function. The benefit of using this function is that it instantly removes extra spaces from data in the whole column. It does not care about the number of different spaces in different cells since every space will be reduced to one. Moreover, whether you have text data, numbers, or alphanumeric data is a good choice.

The experience of using this method is even easier than the Substitute method since you are not dealing with the spaces in this formula. So, here is how you can implement it in your use cases:

• Open the Excel file containing extra spaces between data within a single 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 job of this function is to remove any extra spaces and reduce their count to 1. Click or hit enter to see the result of this formula appear.

• Hold the fill handle and drag it down to implement this formula for all the remaining rows of these columns. Otherwise, double-click to update and implement the formula to the whole column automatically. Regardless of how many spaces are there in every column, you will have their values updated with one space only.

• This step is optional, but if you want to replace the original values with the updated ones, copy them by selecting them and pressing Control + C buttons together. Select the original values and press Control + Shift + V together to paste these values. It will paste values only instead of pasting the formula, which will start giving garbage values.

Part 3. How to Remove Space Before Text in Excel with Find and Replace?

In the previous methods, you used a formula to remove the space between some text. For some users, that may be a very difficult procedure since they have never implemented an Excel formula or function before. If you feel the same, we have another solution that works like a charm here. It is good when you don’t want spaces between the text or numbers.

Similarly, it is good for quick processing since you press one button and remove all the spaces. You can even use this method to remove all spaces in the whole spreadsheet in one go. Additionally, it provides a better user experience with GUI usage.

This method works on most versions of Excel, including the online version, and the best thing is that the experience over all the versions is the same. So, here are the steps you must follow to remove spaces before or between any text in Excel using the Find method.

• Open the Excel file that contains data with extra spaces in it that you want to be removed. Press Control + F buttons simultaneously to open the Find window. There will be a Replace tab. There you need to press that for this procedure. This way of opening the Replace window works on every version, including the online version.

• Now in the Find field, you must insert the space character by pressing the space button once. Meanwhile, the Replace field needs to be left empty. That way, when you run it, all the space characters found will be replaced with no spaces.

• Lastly, press the Replace All button, and you will see values being updated instantly. The Replace window will also highlight the parts where it has made changes.

Part 4. The Best PDF to Excel Converter

Sometimes you receive the spreadsheet data in PDF format. It is usually done to maintain the data integrity. However, this thing is not very useful in some cases, for example, when you need to edit PDF data on a large scale.

In that case, the best solution is to export that PDF document to Excel format, and UPDF is the best choice for such tasks. It is the PDF file organizer for your computer, and it takes PDF files and exports them in different formats. Similarly, you can convert PDF files into multiple other formats with the help of UPDF. Download it now and you can get 2 conversions for free per day.

When talking about PDF to Excel conversion, it brings the following benefits for its users:

• The conversion process is extremely easy.
• You don't need to go through any technicalities for conversion.
• The conversion takes a few seconds, and a new Excel file is created on your computer.
• UPDF provides free PDF to Excel conversions.
• The data integrity is maintained, and all the cells are perfectly placed in an Excel document without any changes to the data.

Since you get to enjoy all these benefits, UPDF brings tons of features to meet all your PDF editing, organizing, and conversion needs including:

• It allows for editing text in the PDF
• You can add drawings and images in PDF
• It is the best PDF reader tool with extensive capabilities on PC.
• UPDF brings UPDF Cloud that makes storing and syncing files across different devices very easy.
• You can annotate PDF files and add comments
• The PDF organization feature allows you to divide one large PDF document into smaller parts
• UPDF brings an OCR feature built-in.
• The UPDF AI features allow you to summarize, translate, explain, and ask questions about PDF documents.

Along with all these features, the best part about UPDF is that it is available on smartphones and computers. So you always have your important PDF documents with you.

Ending Note

All the methods discussed above are useful and easy for everyone to perform. So, whenever you need to remove space before text in Excel, you just need to select the right method for the type of experience you want. The process will become effortless for you since these methods are designed for different use cases.

Sometimes, you don’t get the spreadsheet data in an Excel file. Instead, you get it in PDF format, which can be a little troublesome if you need to replace spaces. Depending on the data size, you can easily remove the spaces with UPDF since it has extensive PDF text editing capabilities. UPDF can also help you organize your PDF file into smaller sections.

Hit the "Free Download" button below to take advantage of the UPDF. Moreover, choose to purchase the UPDF Pro plan with a limited-time offer to make your PDF management even easier.