You are reading the article How To Remove Spaces In Excel With Different Ways updated in March 2024 on the website Cattuongwedding.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 How To Remove Spaces In Excel With Different Ways
Excel Remove Spaces (Table of Contents)
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersIntroduction to Remove Spaces in Excel
There are multiple ways to remove spaces in Excel. The first way we can remove the extra unwanted spaces is with the help of the FIND and REPLACE options (Ctrl + H). We must put a space and replace that with a Blank (Nothing kept). This removes the spaces anywhere from the selected cells. Alternatively, we can remove the spaces using a TRIM function. This removes the spaces at the start and end of the selected cell content. And we can also use the SUBSTITUTE function, where we can substitute space with blank to remove spaces completely.Different Types Of Spaces
It may contain extra spaces before text strings in cells, called Leading spaces.
It may contain extra spaces after text strings in cells, called Trailing spaces.
Datasets may contain extra in-between spaces, i.e.additional spaces in between text.
Datasets may also contain Line breaks with extra space.
We can get rid of these types of spaces quickly & easily with the below-mentioned options:
FIND AND REPLACE
CLEAN and TRIM function to Remove Extra Space of Line Break in Excel
TRIM function is a prebuilt integrated function categorized under Text functions. It Removes all extra spaces from text except a single space between words in Excel.
The syntax or formula for the TRIM function in Excel is:
The trim function in Excel has only one compulsory argument or parameter, i.e., text.
Text: It is cell content from where you need to remove extra spaces in Excel.
The TRIM function removes the text’s ASCII space character (32).How to Remove Extra Spaces in Excel?
Removing extra spaces in Excel is very simple and easy. Let’s understand the working of removing extra spaces in Excel with the help of some examples.
You can download this Remove Spaces Excel Template here – Remove Spaces Excel TemplateExample #1 – Remove Excel Spaces with the help of Find and Replace
In the below-mentioned example, I have a dataset in column D that contains all three types of extra spaces, i.e., Leading spaces, Trailing spaces & Extra in-between spaces.
I have done several character counts for the column D datasets for reference. With the help of the LEN function in column E., I have taken this raw data to another new column (column H) to apply the Find and Replace task.
Now, I have to select the cell range where the Find and Replace parameter needs to be applied to remove extra spaces in Excel.
To activate the find and replace option, press the shortcut key Ctrl + H, find and replace window appears.
It replaces all the blank spaces in the cell.
You can find a difference in character count between both datasets. Where Find and Replace, remove all the blank spaces. In extra in-between spaces content also, it has removed all the blank spaces without retaining a single space between words (This can be rectified with the help of the TRIM function).
Note: This method is only used when you want to remove a leading & trailing type of extra spaces from selected cells or completely remove all the spaces in the data range from Excel.Example #2 – Remove Excel Spaces with the help of the TRIM Function
For reference, I have done several characters counts for the column D datasets. With the help of the LEN function in column E., I have taken this raw data to another new column (column G) to apply the TRIM Function. The Excel TRIM function removes all spaces from text except a single space between words.
Now, In cell H4, let’s apply a TRIM function.
Now, you can observe in cell H4, where the trim function removes the leading space in that cell.
You can find a difference in character count between both datasets where the TRIM Function removes all spaces before and after the text in the cell (i.e., G4, G5, G6 & G7) & consecutive spaces in the middle of a string in the cell G8 & G9.Example #3 – Remove Excel Spaces with the help of the SUBSTITUTE Function
It will remove all the extra Excel spaces, including single spaces between words or numbers.
For reference, I have done several characters counts for the column D datasets. With the help of the LEN function in column E., I have taken this raw data to another new column (column G) to apply the SUBSTITUTE Function.
Now, In cell H4, let’s apply the SUBSTITUTE Function.
Now the function argument dialog box appears:
In the Old_text, I need to enter the character that needs to be replaced; it should be entered between double quotes. i.e., for removal of spaces, it will be” “.
Here, in the New text, I don’t want to replace anything; therefore, it is typed as “.
Now, you can observe in cell H4, where the Substitute function removes leading space in that cell.
You can find a difference in character count between both datasets. Substitute Function removes all the blank spaces; in extra in-between spaces content, it has removed them without retaining a single space between words.Things to Remember about Excel Remove Spaces
Since the extra space is also a character, therefore in ASCII (American Standard Code for Information Interchange) encoding, it has code number 32.
Sometimes, the data appears as line breaks which may contain extra spaces between words; To remove those extra spaces, we can combine Excel TRIM and CLEAN functions to Remove the Extra Space of Line breaks.Recommended Articles
This has been a guide to Remove Spaces in Excel. Here we discuss how to Remove Extra Spaces in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
You're reading How To Remove Spaces In Excel With Different Ways
In this post, we will show you how to remove spaces between characters and numbers in Excel. Whether you want to remove leading or trailing spaces or trim extra spaces between words and numbers in Microsoft Excel, this post will help you get rid of all unnecessary spaces that stick with your data while importing or copying-pasting it from external applications.
Extra spaces may sometimes appear with non-printable characters that may be hard to deal with. When you apply functions to such data, Excel counts these spaces as additional characters and shows incorrect results or throws errors. For example, if you compare two cells with the same content, the result may be incorrect if one of them consists of extra spaces.
Spaces may easily be recognized with the naked eye, but they too could be hard to spot in large data sets. In this post, we will guide you on how to remove these unwanted spaces using different methods.How to remove Spaces between Characters and Numbers in Excel
The following methods will help you remove spaces between characters and numbers in Excel:
Remove spaces using the TRIM() function.
Remove spaces using the SUBSTITUTE() function.
Remove spaces using the Find and Replace feature.
Let us see these in detail.Remove spaces in Excel using the TRIM() function
The TRIM() function is a text function in Excel that is used to fix irregular spacing. It removes all extra spaces from a given text string, leaving no spaces at the beginning and the end of the string and just a single space between the words of the string. When you’re dealing with textual data, using the TRIM() function to strip unwanted spaces would be helpful.
The syntax of the TRIM function is:TRIM(text)
where text refers to the text string or reference to the cell containing the text string.
Let the understand this with the help of an example.
Suppose we have an Excel file containing the ‘Author Name’ and ‘Author Code’ for authors of TheWindowsClub as shown in the above image. The data consists of irregular spacing which needs to be fixed. For this, we may use the TRIM() function as follows:
Place your cursor on cell C5 and type the following function:=TRIM(A5)
To use this method with your data, you will have to apply the function in a new column/cell and then copy-paste the results into the original column/cell. While pasting the data, make sure to select the Values (V) option in Paste Options.
If your data consists of some non-printable characters, TRIM() function will not remove them. For this, you need to use the CLEAN() function. If the data consists of both extra spaces and non-printable characters, you may use a combination of both functions.
If you apply this formula to a number, it will remove leading and trailing spaces but limit the in-between spaces to 1. For removing all the spaces from numbers, you may use the next two methods.Remove spaces using the SUBSTITUTE() function in Excel
SUBSTITUTE() is another text function that lets you replace an existing text with a new text in Excel. You may use this function to remove all the spaces (leading, trailing, and all in-between spaces) from a text string or a number.
The Syntax of SUBSTITUTE() function is:Substitute (text,old_text,new_text,[instance_ num])
Where text refers to the main text string
old_text refers to the specific text that needs to be replaced with new_text
new_text refers to the text that should substitute the old_text
[instance_ num] is an optional parameter that refers to the occurrence of old_text that should be replaced with new_text. If this is not specified, all the occurrences of the old_text will be replaced.
Taking the above example, we may remove extra spaces between characters and numbers in Excel using the SUBSTITUTE() function as follows:
Place your cursor on cell C5 and type the following function:=SUBSTITUTE(A5, " ", "")
The above function will replace all space characters with an empty string. Hence it will also remove the in-between spaces from the author names, as shown in the above image. Therefore it is best suited to remove spaces between numbers. Or in special cases may be used to remove spaces between words or characters.
Also, this function too will require you to apply it in a new column/cell. Once you get the results, you may copy-paste them to your original column/cell.
Read: Excel is slow to respond or stops working.Remove spaces in Excel using the Find and Replace feature
The above results may also be achieved using the Find and Replace feature in Excel. As you may already know, Find and Replace is a handy feature that lets you replace a specific text with another text and is most commonly used for data correction, such as spelling mistakes. However, it can also be used to remove unwanted spaces, such as leading, trailing, or the extra spaces between numbers or characters in Excel.
The key benefit of using this feature is that it can work on a selected cell range or the entire worksheet in one go. So you don’t have to apply functions somewhere else and then copy-paste the results to the original cells. You can simply select the data range and use the feature. However, bear in mind that this will also remove the single space that separates words within a text string. So make sure you choose the appropriate method as per your requirement.
To remove spaces between characters and numbers in the above example, we may use the Find and Replace feature in Excel as follows:
Select the data range.
Select the Replace option.
In the Find and Replace dialogue box, enter a space in the Find what field.
Do not enter anything in the Replace with field. Leave it empty.
This will replace all the spaces with an empty string. Hence all the extra spaces will be removed from your Excel data.
Another important point to note here is the Find and Replace feature considers the leading zeros (0) as spaces. So it will remove all the zeroes from the beginning of your numbers.
Read Next: Convert JSON to Excel using free online converter tools.
In this post, we are going to show you a tutorial to fix #NAME? error in Microsoft Excel. Before talking about the tutorial to correct #NAME? error, let us understand why does this error occurs in Excel.Why do I see #NAME? error in Excel?
Here are the possible reasons due to which you see a #NAME? error message in Excel:
When you have entered a wrong formula name or string or there is some typo in the formula, it returns #NAME? error message in the cells.
It indicates that something is wrong with the syntax you have used and that needs to be corrected.
If you have used a formula that refers to a name that is not defined, you will get this error message.
In case there is a colon missing in the range reference, it will return #NAME? error.
You will see a #NAME? error message if you are using some function that needs an add-in, and the add-in is disabled.
Now, how to resolve #NAME? error in Excel? Unlike some other errors in Excel, e.g., #DIV/0! error, this error can’t be fixed using any error handling function like IFERROR. Here, we are going to list some solutions to fix and correct #NAME? error.How to remove #NAME? Error in Excel
Here are the methods to correct or fix the #NAME? error in your Excel workbooks:
Use formula suggestions or Function Wizard to avoid syntax errors.
Manually check for any typo in the function and correct it.
Check if the name used in the formula is defined in Name Manager.
Ensure that the text values have quotation marks around them.
Enable the add-in required by the used function.
Let have a detailed discussion on these methods now!1] Use formula suggestions or Function Wizard to avoid syntax errors
Microsoft Excel displays a list of matching formulas as soon as you start typing the function in the function bar.
Make sure you use a formula from the suggestions rather than typing it all manually. If you type a formula manually, it increases the chances of typing errors and thus showing a #NAME? error.
You can also use the Function Wizard to avoid any syntactic errors. F2] Manually check for the typo in the function and correct it
If there are some minor typos, you can correct them manually. Just have a look at the function you have used and inspect it to see if there is a spelling error in the formula string.
If so, simply correct it and it will eliminate #Name? error. In case there is some different cause for this error, use another method from this article to fix it up.3] Check if the name used in the formula is defined in Name Manager
In case you haven’t defined a name that has a reference in your formula, it returns #Name? error. So, check and define a name using the Name Manager in Excel. You can follow the below steps to do so:
Reenter the formula with the name you just defined and you won’t see the #NAME? error now.4] Ensure that the text values have quotation marks around them
If you are using text references in the formula, you must enclose them in quotation marks. Else, you will get a #NAME? error message in the cells.
The solution is that look closely at the formula string and check if the quotation marks are added properly. If not, simply put the quotation marks around the text references. This will fix the error.5] Enable the add-in required by the used function
Some custom Excel functions need add-ins to work. For example, the EUROCONVERT function needs Euro Currency Tools add-in to work. Also, various statistical and engineering macro functions require Analysis ToolPak add-in to be enabled. So, simply enable the required add-in and it won’t return #Name? error.
Here are the steps to enable the add-in in Excel:
In the Add-ins dialog window, enable the relevant checkboxes to turn on the required add-in.
Tap on the OK button and see if the #Name? error is fixed.
That’s it! Hope this article helps you fix #NAME? error in Microsoft Excel.
Now read: How to Fix Runtime Error 1004 in Excel
If you are tired of saving images in the default formats they come in and would rather save them as the type that you want straight away. This article will introduce you to several different extensions you can use to instantly save images in the exact format you require.
Related:How to use TikTok straight from the Opera sidebar.
Saving images from the Internet is something that everyone does in their day-to-day life. See something you like? you either screenshot it or save the image. Unfortunately, a lot of websites are now starting to use Google’s new webp image format which is extremely compressed and helps with site load times. However, it isn’t the best format to use for anything else.
While you can simply open these images in programs like Photoshop, GIMP, Paintshop, etc then convert them to your desired format there are easier ways. Using a variety of different extensions you can quickly and easily save images in the exact format that you need the file in. This saves you from having to convert it into a different format after you have downloaded it. Below you will find three of the best extensions available for this exact task!
For those wanting more than one save format option: Save image as Type.
This is my personal favourite from this list as it allows you to quickly and easily save images in three of the most popular file format types, JPG, PNG, and WebP. It doesn’t matter what the original file format is, you can save the file exactly how you want to without any quality loss. So how exactly do you use it?
First head on over to the Chrome Webstore and add Save Image as Type to your browser, Chrome, Edge, Brave, Opera, etc. It’s available for all of them.
Now just select the image format that you want to use and the file will be saved for you.
If you just want to save everything as a PNG file perhaps the next option will be better for you.
If you just want to save images as PNG files automatically: Save Image As PNG is the best option.
First head on over to the Chrome Webstore and add Save Image as PNG to your browser, Chrome, Edge, Brave, Opera, etc. It’s available for all of them.
Need to save images as JPEG, JFIF or JPG? Try Change JPEG/JFIF to JPG.
Another great option you can use to save images from the Internet in specific formats is Change JPEG/JFIF to JPPG. This extension is a little different to the others as it automatically saves JPEG/JFIF files as JPG files. While this may be handy for some people it isn’t for everyone so unless you have a very specific niche requirement I’d probably stick to one of the other options shown above.
Mistakes like a missing period or incorrect spelling may change the entire output. Fortunately, Microsoft Excel provides a set of built-in tools that can prove useful to detect inconsistencies. In this post, we will discuss how to find differences or discrepancies in Excel and get a clean data sheet.What does discrepancy mean in Excel?
My brand name & Co. total sales = 587
My brand name & Co total sales = 587
As you can see the period is missing in the 2nd example (Co total sales) compared to the 1st one (Co. total sales). This may look normal to you since you already know what is correct, but the computer does not consider it similar.
Likewise, discrepancies in Excel may also arise due to input errors like space after a value, uppercase or lowercase letters, duplicate entries, or inconsistent data formatting. Therefore, we have this detailed guide to explain how to find discrepancies in Excel easily.How to find discrepancies in Excel
If that doesn’t help, you can proceed with the below methods to find discrepancies in Excel.
Use Excel Filter to find differences
Find discrepancies in Excel using Conditional Formatting
Detect discrepancies using Excel Add-ins1] Use Excel Filter to find differences
Using the Filter function is one of the easiest methods to find out discrepancies in data, especially for Excel sheets with large data and spelling errors. Here we will consider an example of Class 5 test results. Under the Results column, there are two categories Pass and Fail, and some are spelled incorrectly.2] Find discrepancies in Excel using Conditional Formatting
Conditional Formatting helps you to identify and compare two or more rows or columns with each other to identify mismatched data. The quickest way to find the difference would be to highlight cells for Duplicate or Unique entries.
However, you can also use other rules like Top/Bottom rules, Data Bars, Color Scales, or Icon Sets to find discrepancies in Excel data.
You can use the IF and IS function to help you to compare cells and detect any differences. So, here, you can use the IF function to find out which cells are identical and correct (TRUE) and which cells are incorrect (FALSE).
Alternatively, you can combine IF and IS functions like ISNUMBER, ISERROR, ISBLANK, etc. to analyze the set value and return TRUE or FALSE based on the outcome.
You can also use the VLookUp function, HLookUp function, or Match function to find any discrepancies in Excel.
Read: Advanced Microsoft Excel Tips and Tricks4] Detect discrepancies using Excel Add-ins
Or, you can use the Analysis ToolPak (Excel Add-ins) to analyze and detect discrepancies in Excel data.
Read: How to manage Add-ins in Microsoft Office
Alternatively, you can also use the Excel Compare tool to compare Excel sheets and find discrepancies in the data set.How do I check data correction in Excel?
When you have an Excel sheet where other users can enter their data in a set format specified by you, that’s when you use Data Validation. This tool helps you to correct any anomalies in the data that do not follow the set guidelines or when entered in the wrong format. In this case, Excel prompts an error alert which means you need to find the differences and correct the data using the Data Validation tool.
To know more about how to apply Data Validation to Cells in Microsoft Excel, you can follow our detailed post.How do I find a mismatch in a column in Excel?
While Conditional Formatting (as explained above) can help you find mismatched data easily, the VLookUp function allows you to compare individual cells.
If using the Unique Equals Operator to compare columns in Excel, create a new Result column. Now, in the Result column, add the formula to compare individual cells. For example, =I2=J2, and it will return the result as False if the data doesn’t match. For the ones that match, the result will be True.
I hope this helps.
Introduction to Freeze Panes in Excel
Freeze Panes in Excel are used to fix any frame, row, or section of the table to access the data located below so that the user can also see the header’s name. There is 3 type of Freeze Panes option available in the View menu tab under the Window section, Freeze Panes, Freeze Top Row, and Freeze First Column. Freeze Panes are used to freeze the worksheet from where we keep our cursor. This freezes both the row and column both. Then to freeze a Row and a Column, we have a separate option to freeze each of them. Once we do that, we will see some portion of the worksheet will only move once we unfreeze it.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
A Frozen top row to know which parameters we are looking at during a review:
Before Freezing Top Row:
After Freezing Top Row:
This shows how the same dataset looks with a frozen row. This makes it easy to know which parameter we refer to when analyzing data beyond the first few records in the workbook.
A frozen first column to know which record we are evaluating for a particular parameter.
Before freezing the first column:
After freezing the first column:
Freezing Panes allows us to split the dataset into multiple parts to ease analysis: The worksheet gets split into different parts, which can be browsed independently. The figure above compares the dataset with and without the first column frozen. The Grey Lines in the middle of the worksheet indicate where the rows and columns have been frozen.How to Freeze Panes in Excel?
The Freeze Panes feature is not very complicated if we know the database we are working with. In the next few paragraphs, we will learn how to use the features associated with freezing panes and using them for analysis.
Here are a few examples of Freeze Panes in Excel:
You can download this Freeze Panes Excel Template here – Freeze Panes Excel TemplateFreeze Top Row – Example #1
To do this, we have to perform the following steps:
from the Excel toolbar. Select
from the view options; this will open a dropdown menu with options to select the rows or columns we want to freeze. Select
Freeze Top Row; this
will keep the active worksheet’s top row in place and allow us to browse the rest of the data without disturbing the top row.
A Tiny grey straight line will appear just below the 1st row. This means the first row is locked or frozen.Freeze First Column – Example #2
Next, we look at the next most commonly used function in the Freeze Pane feature, freezing the first column. This can be done using the following steps:
Select Freeze Panes from the view options. From the dropdown menu, select Freeze First Column, and this would freeze the first column in place, allowing us to browse the rest of the data without disturbing the first column.
A Tiny grey straight line will appear just below the 1st Column. This means the first column is locked or frozen.
These features can be used simultaneously, making it easier for us to analyze data. As we have seen in the examples, knowing the table’s basic structure helps us decide what we want to freeze.Freeze First Row and First Column – Example #3
Freeze the first row and first column.
Here is an example of the practice table with the frozen first row and first column.
This brings us to the most useful function in the freeze panes feature, freezing multiple columns and rows.
I like to use this function the most because it enables the user to freeze rows and unfreeze rows and columns based on any number of parameters depending on the structure of the worksheet’s data.
To freeze the first row and first column, we need to perform the following steps:
Select Cell B2 from the worksheet.
Now, from the view options, select Freeze Panes. From the dropdown that appears, select the first option, Freeze Panes.
These actions freeze the first row and first column in place.Freeze Multiple Columns – Example #4
We can use similar steps to freeze multiple rows and columns. The following steps illustrate this:
Select any cell above which the rows and columns have to stay in place:
Repeat steps 2 and 3 from the previous illustration to freeze all rows and columns above and left of the selected cell.
The solid grey lines indicate that the rows and columns on the top left of the sheet have been frozen. We can also choose a whole row above which we need data to stay in place or a column.
Freeze panes in Excel are an option that makes it very easy for us to compare data in large datasets. In fact, freezing panes in Excel are so useful that software providers provide additional features based entirely on freezing panes in Excel. One such example is the ability to freeze and unfreeze multiple worksheets and tables simultaneously, which many software vendors provide as a product.Things to Remember
Freeze panes in Excel are a default configuration that can freeze data to the left of the boundary column or above the boundary row, depending on what we choose as a boundary. There are add on available from various software providers to enhance these.Recommended Articles
This has been a guide to Freeze Panes in Excel. Here we discussed how to freeze panes in Excel and different methods to freeze panes in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles to learn more –
Update the detailed information about How To Remove Spaces In Excel With Different Ways on the Cattuongwedding.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!