Remove Unwanted Spaces From Text String

By


Many times we receive data in excel which is not properly formatted. It may contain some unwanted spaces which will waste our extra time to remove them.

In this blog post we will learn to remove these unwanted spaces from text string in excel. For this we will use TRIM function which is readily available in excel.

Where to find TRIM function on Excel Screen:

TRIM function can be found under ā€œText Functionā€ category under ā€œFormula Tabā€ as shown in below image:



Once we click on ā€œTextā€ category, we can see list of various Text Functions available in excel. TRIM function is highlighted in red in below image.


Once we click on TRIM option as highlighted above, we will get the function argument dialog box as shown in below image:



Syntax of TRIM Function:

The Syntax of TRIM function is as below:

=TRIM(text)



Arguments of TRIM Function:

To use the TRIM function we have to use only one arguments:

text: This is the only one and mandatory argument in which we can provide text string from which we want to remove unwanted spaces.

Example of TRIM Function:

Letā€™s learn about using this TRIM function with the help of simple example. We have prepared a dummy data as shown in below image. In this dummy data we have listed multiple text strings in column A and we will remove unwanted spaces from these text strings. The output will be displayed in Column B.


For this, select the cell in which we want to apply TRIM function. In this case we have selected cell ā€œB2ā€ where output of TRIM Function will be displayed. In cell ā€œB2ā€ enter TRIM formula as shown in below image.


Once we hit enter button, we can see the all the unwanted spaces in selected cell ā€œA2ā€ are removed.


To get the desired result in remaining cells as well from column B then we can just copy and paste the formula applied in cell B2. This will result in removal of all the unwanted spaces from other cells as well.



What TRIM Function Can Do?:

1. TRIM Function can remove unwanted spaces from beginning of the text string.

2. TRIM Function can remove unwanted spaces from the end of the text string.

3. TRIM function can remove unwanted spaces which are present in between two words (it will keep only once space between two words and will remove all other multiple spaces from two words).
Microsoft Certified Excel Expert, Enthusiastic to teach excel tricks and basic excel concepts which will help to save time and efforts in our daily office work.

Comments

Post a Comment

Related Topics..

Excel Shortcuts Ctrl A to Ctrl Z

By
Image
Use of keyboard shortcuts while working in excel will be a great help for us. It will save a lot of time. In this post we are going to see Excel Keyboard Shortcuts for alphabets A to Z while using Ctrl key. Below table will give you a brief idea for each shortcut and its functionality: Now, we will see in detail how each of above listed shortcut combinations will work: Ctrl + A :  It will select all content present on the worksheet. If active cell is in the table then it will select entire table. Ctrl + B :  It will convert selected text into Bold . Ctrl + C : It will copy the selected content. The content which is copied can be seen in the Clipboard. Ctrl + D :  It will fill down the content from upper cell. For example we have inserted a number ā€œ1ā€ in the cell A1. If we select the cell range from A1:A5 and press Ctrl + D, then this number ā€œ1ā€ will automatically fill down in the entire cell range . ...
Read more

Fill Up, Down, Right & Left in Excel

By
Image
Excel has many great tricks which can help us to save our time while working on big data. In this blog post we are going to see one of those tricks where we can fill the data to the upwards of the selected cells, to the downwards of the selected cells, to the right of the selected cells and to the left of the selected cells. There are many situations where we may need to copy the data from selected cell to the upward, downward, left and right cells. But how can we do so?? In excel we can do so with the help of ā€œFillā€ command. This command we can found in: Home Tab --> Editing Group --> Fill Command. Letā€™s take an example. We have inserted a text ā€œTejrajā€ in a cell ā€œD5ā€. Now we will see how to fill this text from cell D5 to Up, Down, Right & Left. Fill Down: 1. Letā€™s assume that we have to fill the data from cells D5 to D10. For this, select the Cell Range D5:D10. Keep in mind that the data from 1st cell of the selected Cell Range will fill down in entire selection. 2. Select ...
Read more

Excel Pivot Table

By
Image
Pivot Table in excel is a very powerful feature which helps us to to organize and summarize the data in very effective manner, that too without using any complex excel formula or functions. In this blog post we will see how to create Pivot Table in Excel. Below is the dummy sales data for last three years (2018, 2019 & 2020). In this sales data employees A, B, C & D sales various electronic components like Resistor, Capacitor, Inductor, Diodes & LED in all South, East, North & West region of India. Creating Pivot Table: Now we will see how to create Pivot Table in Excel. Pivot Table feature is available in Insert Tab. Pivot Table command is available in "Insert Tab" under "Tables Group" as shown in below image. Once you click on Pivot Table command, ā€œCreate Pivot Tableā€ dialog box will appear on screen. This is like as shown in below image. This ā€œCreate Pivot Tableā€ dialog box is very important to create Pivot Table. There are two important options a...
Read more

How to Remove Duplicate Entries from Multiple Columns in Excel

By
Image
In our last blog post, we have learned to remove duplicates entries in excel . In this blog post, we will go little advanced to remove the duplicate entries from multiple columns. Removing Duplicates Entries from 02 Columns: We will learn to remove duplicate entries from the data as shown in below image. The entries which are duplicate in both the ā€œFruitsā€ column and ā€œPrice/Kgā€ column are highlighted with different colors. To remove duplicate entries in these 02 columns, follow the steps provided below: 1. Select the data from which we want to remove the duplicates. 2. Go to ā€œDataā€ tab and under ā€œData Toolsā€ we can find command named as ā€œRemove Duplicatesā€ as highlighted in below image. 3. Once we click on ā€œRemove Duplicateā€ command, one dialog box with same name ā€œRemove Duplicateā€ will appear on our excel screen. 4. If we check the checkbox for ā€œMy Data has headersā€ then the header row from our selection will be excluded automatically and under ā€œColumnsā€ section we will see the name o...
Read more

Data Bars in Conditional Formatting

By
Image
In continuation to series of posts on Conditional Formatting, we will learn how to use Data Bars in Conditional Formatting through this post. Conditional Formatting command can be found in Styles group under Home tab as shown in below image. Click on the Conditional Formatting option and select ā€œData Barsā€ option from the list of appeared options. We can now see all the ā€œData Barā€ options available in excel. These ā€œData Barā€ options are categorized into two groups.  1. Solid Fill 2. Gradient Fill: There are six different ā€œData Barā€ colors available in each group. These colors are listed below: 1. Blue Data Bar 2. Green Data Bar 3. Red Data Bar 4. Orange Data Bar 5. Light Blue Data Bar 6. Purple Data Bar Each group with their respective colors are shown in below image: Solid Fill: Gradient Fill: Now we will see how we can apply these ā€œData Barsā€ on our data. For this we have created dummy data of few employees and Incentive received by them. Example with Gradient Fill: 1. Select all...
Read more

Title Bar in Excel

By
Image
Title Bar is the bar holding title of the excel file. But apart from showing title, it also contains many other important functionalities. so let's get familiar with Title Bar in this blog post. Title Bar is divided in 03 parts: 1. Quick Access Toolbar 2. File Name 3. Minimize, Maximize/Restore, Close buttons Letā€™s see in detail about this. Quick Access Toolbar: ļ»æ The commands which we are using frequently can be added to Quick Access Toolbar. Following steps will help you to add desired commands here. 1. Right click on the desired command. (In below screenshot we have selected ā€œ Format Painter ā€ as a desired command) 2. Select option ā€œAdd to Quick Access Toolbarā€. 3. Now, you will see ā€œ Format Painter ā€ command is added in the Quick Access Toolbar. 4. To remove desired command from Quick Access Toolbar, just right click on the command and then select option ā€œRemove from Quick Access Toolbarā€. 5. No...
Read more

SUMIF Function in Excel

By
Image
In this blog post we will learn to about SUMIF function in excel. Whenever we want to calculate the SUM based on certain criteria or condition, we can use the SUMIF function. Where to find SUMIF function on Excel Screen: SUMIF function can be found under ā€œMath & Trigā€ function category under ā€œFormulasā€ tab and under ā€œFunction Libraryā€ group as shown in below image: Once we click on ā€œMath & Trigā€ function category, we can see list of various Math & Trig functions available in excel. SUMIF function is highlighted with blue color in below image. Once we click on SUMIF option as highlighted above, we will get the function argument dialog box as shown in below image. Syntax of SUMIF Function: The Syntax of SUMIF function is as below: =SUMIF(range, criteria, [sum_range]) Arguments of SUMIF Function: SUMIF function takes three arguments as listed below: range : It is cell range which you want to evaluate or in other words, it is cell range from which we want to apply condition or ...
Read more

Select all cells highlighted with specific color at a time

By
Image
Many times we apply colors to cells in excel sheet which will help us to identify important cells just by looking at the data. But what if we use multiple colors to highlight data and later on want to select all the cells which are highlighted with specific color only???  YouTube Video: For example, we have listed some values in excel sheet and highlighted few cells with red color and few cells with yellow color. Now, we want to select all cells which are highlighted with only yellow color at a time. But how????  Don't worry... We will provide you a fantastic solution for this!! Follow below steps:  1. Select entire data. 2. Press Ctrl + F. "Find and Replace" window will appear. 3. Click on Options button. 4. "Find and Replace" window will expand with some additional options. Click on Format button. 5. "Find Format" window will appear. Click on Fill tab and select yellow color. You will s...
Read more

Adjusting Row Height & Column Width in Excel

By
Image
In this blog post we will see how we can adjust Row Height and Column Width of cells in excel sheet. The options to adjust Row Height and Column Width in excel are available in Home Tab under cells group. Once we go to Cells group, click on ā€œFormatā€ command and then under Cell Size we can find all the options to adjust Row Height and Column Width. Letā€™s learn more about this with real time example. We have a dummy data of marks obtained by five students with us. But the data is not well organized. The Row Height and Column Width of cells which contain this dummy data is not equal which giving an ugly look to our data. So letā€™s adjust Row Height and Column Width for these cells so that our data will look more attractive. Row Height: Select all the cells for which we want to adjust Row Height. Select "Row Height" option under ā€œFormatā€ command as shown in below image. Row Height dialog box will appear on screen. Here we can enter the value to adjust Row Height. We have provided ...
Read more

Text Filter in Excel

By
Image
In our last blog post we have seen what is filter and how to apply filter in excel . Click here in case you missed to read about this. In this post we will see details about ā€œText Filters in Excelā€. Text Filter can only be applied to columns which contains Text values. We will see all the sub types of Text filters in detail with below example. We have a data of some electronic products sold in East, West, South and North regions from 1st Aug 2019 to 1st Apr 2021 with us. As already stated, we can apply text filters to the column which contains text values. So in this case we will apply text filter for the ā€œProduct Nameā€ column.  Click on the drop down button which is available on header cell ā€œProduct Nameā€. You can see the ā€œText Filtersā€ option as highlighted in below image. We can filter data available in this column based on below criteriaā€™s: Equals Does Not Equals Begins With Ends With Contains Does Not Contains Custom Filter Now, we will see each of these criteria in detail: ...
Read more