User Posts: Afia
0
How to Create New Sheet from Template Using Macro in Excel
0

If you want to create a new sheet from a template using macro in Excel, you have come to the right place. Here, we will walk you through 3 easy methods to do ...

0
How to Zoom in on Map Chart in Excel (5 Easy Methods)
0

An Excel Map chart is used to represent and compare values over geographical regions. For better understanding and to make the values of the Map chart more ...

0
How to Make a Forest Plot in Excel (2 Suitable Examples)
0

If you want to make a Forest plot in Excel, you have come to the right place. Although Excel does not have any built-in Forest plot, we will show you some easy ...

0
How to Draw Engineering Drawing in Excel (2 Suitable Examples)
0

If you want to draw engineering drawing in Excel, you have come to the right place. Here, we will demonstrate to you 2 examples that will help you to do the ...

0
How to Add Same Header to All Sheets in Excel (5 Easy Methods)
0

If you want to add same header to all sheets in Excel, you have come to the right place. Here, we will demonstrate to you 5 easy methods to do the task ...

0
How to Reconcile Vendor Statements in Excel (2 Easy Methods)
0

If you want to know how to reconcile vendor statements in Excel, this article is for you. Here, we will walk you through 2 easy and effective methods to do the ...

0
How to Automate Financial Statements in Excel (with Easy Steps)
0

If you want to automate financial statements in Excel, you have come to the right place. Here, we will walk you through some easy steps to do the task ...

0
How to Calculate Present Value of Uneven Cash Flows in Excel
0

If you want to calculate the present value of uneven cash flows in Excel, you have come to the right place. Here, we will walk you through 3 easy methods to do ...

0
How to Change Marker Shape in Excel Graph (3 Easy Methods)
0

Excel automatically creates a shape for the marker while creating a Line, (XY) Scatter, and Radar chart. These data markers help the reader to understand the ...

0
How to Add More Gridlines in Excel (2 Easy Methods)
0

In Excel, we can display 3 types of gridlines, they are Horizontal gridlines, Vertical gridlines, and for 3D charts, we have Depth gridlines. Excel ...

0
How to Insert Sequential Page Numbers Across Worksheets
0

If you have several worksheets in your Excel file, and you want to print your Excel worksheets, you must insert sequential page numbers across the worksheets. ...

0
How to Insert Character Between Text in Excel (5 Easy Methods)
0

If you want to insert character between text in Excel, this article is for you. Here, we will walk you through 5 easy and effective methods to do the task ...

0
How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
0

If you are looking for Excel Bar Chart ignore blank cells, you have come to the right place. Here, we will walk you through 4 easy and effective methods to do ...

0
How to Make Excel Look Pretty (16 Easy Formats)
0

If you want to make your Excel more visible and presentable, you must make the Excel look pretty. Although the main purpose of Excel is to calculate and ...

0
[Fixed!] Excel Status Bar Not Showing Count (with Quick Solution)
0

If your Excel status bar not showing Count, and you are looking for a solution then you have come to the right place. In this article, we will demonstrate to ...

Browsing All Comments By: Afia
  1. Hello Mark W
    I hope after reading my reply, you will be able to solve the problem.

    1. First, let me explain to you how the TRIM function works.
    Suppose, you have the name “ Joe Louis “, and you can see this name has leading, middle, and trailing spaces. In that case, the TRIM function will work.
    The result will be like “Joe Louis”.

    However, if the name is like “ Joe Lou is “, the TRIM function will only remove the leading and trailing space of the name. It will not remove the space between letters.
    The final result of the above name will be “Joe Lou is”.

    Now, suppose you have a number like “ 12 24 5 6 “, in this case, the TRIM function will only remove the leading and trailing spaces. Therefore, after applying the TRIM function.
    The number will look like “12 23 5 6”.

    I hope that now you will easily understand in which situations, the TRIM function works.

    2. After that, let me explain to you how the SUBSTITUTE function works.

    If you have a number like “ 1 2 45 7 “ then the SUBSTITUTE function will eliminate all the spaces. Here, suppose the old number is in cell C4. Therefore, you have to type =SUBSTITUTE(C4,” “,””).
    After that, the number will become like “12457”.

    Next, if you have a word where the letters have spaces between them like if you have “ Yell ow “ in a cell, the SUBSTITUTE function will remove the spaces. Here, suppose the old number is in cell C4. Therefore, you have to type =SUBSTITUTE(C4,” “,””).
    The result will be “Yellow”.

    However, if a Text has spaces between words then we have to identify and add the space in the SUBSTITUTE function properly. Let’s say, the name “ Adam Smith “ is present in cell C4. You can easily notice that there are three spaces between Adam and Smith. Along with that, there are lading and trailing spaces. we have to type =SUBSTITUTE(F16,” “,” “), here you to give three spaces in between the first double quote. Along with that, make sure to keep one space between the second double quote, otherwise both the words will merge into one word.
    The outcome will be “Adam Smith”

    I hope that when using the SUBSTITUTE function if you can identify the spaces between words, and add the space properly in the formula, your problem will be solved.

    3. Let us now discuss how Find and Replace works.
    Find and Replace is a useful feature to replace spaces between numbers in a cell. If you want to replace spaces between numbers, Find and Replace is an effective and easy way.

    However, For different numbers of spaces between words, we have to identify those spaces, and in the Find what box we need to press those Exact numbers of spaces. Otherwise, the Find and Replace will not work. Therefore, if different numbers of spaces between text are present in different cells then the cells need a unique number of spaces in the Find what box.

    I hope you can identify the spaces between words, and in the Find what box you can press exactly the same number of spaces. Hence, your problem might be solved.

    4. When different cell content has a different number of spaces, Power Query is extensively useful to remove those spaces.
    As in your comment, you did not mention anything regarding Power Query, I highly suggest you use Power Query. I am hopeful that it will solve your problem.

    Thank you for your comment. I hope you will now be able to solve your problem. If, however, these methods still do not work for you, please share your Excel file in the comment section. This will help me to understand the problem, and I will try my best to solve the problem.
    Regards!

ExcelDemy
Logo