How to Count the Number of Spaces or any Character in a text

Friends, 

In this article, we will learn how to count the spaces or any special character or alphabet in a text in any cell in Microsoft Excel.



How to count the number of Characters in Excel?

LEN function is the answer. But LEN function alone will not help you to count how many spaces are there in a text string or in a sentence.

There is no specific Function in Microsoft Excel which can do this task. Therefore, we must use some trick to develop an Excel 
Formula which will do it for us. So, let’s do it. 

Here we will use two nested function to do the job, one LEN function to count the number of characters in a text and second SUBSTITUTE function which is used to replace specific text in a text, I have discussed in my last article.

How to Count the Number of Spaces or Alphabet or any Character in a text?

As we already know that SUBSTITUTE function replaces specific character and then it displays the modified text. For example, suppose replace the spaces by nothing (only opening and closing double quotes) between the words in the text “The quick brown fox”, the resultant text will be “Thequickbrownfox”. The spaces will be removed.

Now if we count the number of characters in this text using LEN function and deduct this number from the old text number (i.e., including spaces), we will get the number of spaces.

In the below image, I have calculated the number of spaces in a text step by step.

How to Count the number of Spaces in any Text in a cell

Step 1 : Get the number of characters in the given text using LEN function.

Step 2 : Replace the spaces using SUBSTITUTE function.

Step 3 : Count the number of characters in the text which is the output of SUBSTITUTE function.

Step 4 :  Now deduct the numbers and the difference is the number of spaces in that text.

The excel formula to count spaces or any other character or alphabet is =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

How to Count number of words in a text in excel?

Using the above excel formula we can also count the number of words in a text string. This is a tricky part, as we know in a sentence there are spaces between words, therefore, if we count the number of spaces and then add one with this number will be the number of words in a text (provided no double spaces are entered in the text).

So, the formula will be =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1

You can further modify the formula by using TRIM function to remove the spaces before and after the text.

Now the formula will be =TRIM(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1)


Hope you have enjoyed this article "how to count the number of spaces in a text in Excel".
Thank you for reading…

SUBSTITUTE

Today in this article I will discuss about SUBSTITUTE function in Microsoft Excel.

As the name of this function indicates, SUBSTITUTE is used to replace an old text by new text within a text or string, for example, suppose in any cell a text is written as “Excel” now you want to replace the letter ‘x’ by ‘Y’; this change can be done using SUBSTITUTE function. You can also find and replace cell reference in excel formula or find and replace part of  formula in excel.

SUBSTITUTE function, if used with some other function(s) i.e., nested it can solve difficult tasks which I will show you in my next article.

Let us discuss about SUBSTITUTE function in detail. In my next article I will discuss how to use SUBSTITUTE function. Generally this function is used to replace a particular character or number or special character.

Syntax

The syntax for the SUBSTITUTE function in Microsoft Excel is...

=SUBSTITUTE( text, old_text, new_text, [instance_num] )

Parameters or Arguments

text
The original string to use to perform the substitution operation.

old_text

The existing characters to replace.

new_text

The new characters to replace old_text with.

instance_num

Optional. It is the nth appearance of old_text that you wish to replace. If this parameter is omitted, then every occurrence of old_text will be replaced with new_text.

Returns

The SUBSTITUTE function returns a string/text value.

Note : SUBSTITUTE function is case sensitive. This function first searches for the provided text and then if found it replaces that character or string with the new text. Therefore, while writing this formula, please do not change the case of the alphabets otherwise this function will not work or it will give you an error or the replacement will not be proper. 

Example :

How to use SUBSTITUTE function?

As shown in the below picture, let suppose I have a text as  “Excel-Solutions-Basic-and-Advanced” and I have to replace the dash(-) with a blank space, it can easily be done using “Find and Replace” option in Microsoft Excel by pressing Ctrl+F but using a function is wonderful.

How to use SUBSTITUTE function in Excel formula

So, the excel formula of SUBSTITUTE function will be =SUBSTITUTE(B3,"-"," ").

Here SUBSTITUTE function omits the DASH (-) and inserts a blank space and the resultant text now is "Excel Solutions Basic and Advanced".

Hope you have enjoyed this article, in my next article I will show you how beautifully we can use this very basic function to resolve difficult situations.

Thank you for reading…

Fill Blank Cells With Value Above or Below the Cell or Zero

Hi Friends,

I hope most of us has faced a typical problem in Microsoft Excel regarding filling the blank cells in any report. Although filling up empty cells with the value above or below the current cell makes Excel reports clumsy but to fill the blank cells with the value above or below is also very useful in matching the data column wise or row wise (using CONCAT and VLOOKUP) and lastly we fill it by Copy & Paste, a tedious and monotonous job.

Today, one of my friends requested me to show the trick to fill the blank cells by the above value or value below the cell.

Today I will show you few tricks to fill the blank cells by the value above the cell or below the cell step by step in any table in Excel and in PivotTables.

To fill the blank cells, we need to first detect and select the empty cells in Excel table then we will fill these cells by the value.

Let suppose we have a table (for this example I have created a small table) as shown below.

How to Fill Blank cells in Excel

Now the task is to fill the blank cells in the column ‘D’ by the value above a blank cell and the second task is to fill the ‘F’ column by the value below the blank cell.

To complete the above task, first we must find and select the blank cell in this table.

The steps to select the blank cells in Excel Table.

1. Select the entire table or one particular column to be filled.

2. Press Ctrl+G or F5 (Function key) which will open a new window 'Go To' as shown below.

Go To Window to Select Blank Cells

3. Click on the ‘Special…’ button shown in the above image by red rectangle.

4. Another new window will appear, ‘Go To Special’, select the Blank radio button as show in the below picture.

Go To Window to Select Blank Cells

5. Click OK.

6. You can notice that the blank cells are now selected as shown in the below image.

Blank Cells are Selected

7. Now the trick is, type ‘=’ (do not select any cell, it is already selected) and press ‘UP’ arrow to select the above cell with value and press Ctrl+Enter.

8. It’s done.

Blank Cells are Filled in Excel

But did you noticed that the second task is not accomplished!!! 

So, what to do?

For the first problem, to fill the blank cells by the value above a cell we should select the ‘D’ column only then apply the above steps it will be done.


For the second task, select the column ‘F’ and press ENTER button unless the cursor moves above the cell which is filled by data and follow the same steps.

Blank Cells are filled using Cell Reference

Note : All the cells we filled are in reference to another cell, therefore, please don’t forget to use paste special to remove the formula.

How to fill blank cells by zero or any other number or text?

To do this follow the below steps.

1. Select the column or row.

2. Press Ctrl+G or F5 (Function key) which will open a new window Go To as shown below.

3. Click on the ‘Special…’ button shown in the above image by red rectangle.

4. Another new window will appear, ‘Go To Special’, select the Blank radio button as show in the above picture.

5. Click OK.

6. You can notice that the blank cells are now selected as shown in the below image.

7. Now Type your TEXT or NUMBER or ZERO and press Ctrl+Enter.

8. And you are done.

Blank Cells Filled with Text or Numbers or Zero

Isn't it awesome!!!

How to fill Blank cells in Pivot Table?

Now let us learn how to fill blank cells in a PivotTable. I have made a Pivot Table from the above table as shown in the below picture. You can notice that there are blanks below the Product Category and Employee Name column, we need to fill these blank cells by the same value in the above cell.

How to Fill Pivot Table Blank cells

Very simple steps are available in Pivot Table, please follow the below steps.

1. Select any blank cell in PivotTable where you want the items to be repeated.

2. Go to Analyze

3. In the Active Field menu, you will see an option, Field Settings.

4. Click this button, show below.

PivotTable Field Settings Option

5. One new window ‘Field Settings’ will appear

6. Select the second Tab, Layout & Print.

7. Tick the Checkbox, Repeat the item labels.

8. Click OK, its’ done.

Fill Blank Cells by repeating data in Pivot Table

Blank cells filled in Pivot Table

I hope this article is informative & you enjoyed this article.
Thank you for reading…

TEXT

Hello Friends,

Today I will discuss about such a function which is not only very small but when implemented it can help you a lot.

The function is TEXT Function.

The Excel TEXT function returns a number in a specified number format, as text. It sounds very simple and plain sentence to us, let us learn how we can use TEXT function in our Excel formula.

This function can convert any number to string or text.

Syntax

The syntax for the TEXT function in Microsoft Excel is as under.

=TEXT(value, format)

Parameters or Arguments

value

The value to convert to text.

format

The format used to display the result.

Returns

The TEXT function returns a string/text value.

Example 1 :

How to use TEXT function?

The basic function of Text function is to convert any format data to text format. Below examples of TEXT function is on Date format.

Suppose you are given with few dates in a column and it is required to convert date format, say 23-04-18 to 23-Apr-18. Using TEXT function, you can do it in few seconds. Below picture will illustrate this feature better.

How to use TEXT function

Example 2 :

How to concatenate Short Date with Text?

We occasionally want to print the date automatically with a string, like Dashboard as on 23-Apr-18.

We can do it in two ways…

First : Placing date in one cell and the text in another cell then use CONCATENATE or CONCAT with TEXT function as shown in the below image. But it is also not so pleasing. We need some more automation. So, you can use the second method.

How to Using TEXT with CONCAT to display Dynamic Date with Texts or Strings

Second : More automation!!!

In the above example our excel formula is =CONCAT(A2," : ",TEXT(B2,"dd-mmm-yy")).

Now we will replace the cell reference ‘B2’ by another function TODAY() and we are done.  The final excel formula becomes =CONCAT(A2," : ",TEXT(TODAY(),"dd-mmm-yy")) as shown in the below picture.

How to Use TEXT function with CONCAT function to display Dynamic Date with Texts or Strings

Example 3 :

How to put zero before any integer number?

How to pad number with zero?

This was very useful when I was at telecom sector. We occasionally needed to put zeros before few numbers. Let us learn this.

Suppose we need to convert few numbers in 5 digits but the umber may be any length number. Say, we have numbers as 123,1234,12 and so on. If we want to put zeros before these numbers we must use TEXT function as show in the below image.

Note : You should use that number of ‘0’s which matches the length of the desired number.

Below image will describe it better.

Pad numbers with zeros using TEXT function

Hope this discussion or tutorial was helpful to you….

Thanks for reading…

LARGE

We already discussed about ARRAY in Excel earlier in this website. Today we will discuss about another function when used with array can prove its immense power in excel formula in certain cases.

Let us have one example to understand where this function will better perform.

Let suppose, there are 100 numbers and you have to provide the sum of top 10 numbers and the numbers are not sorted. If you sort these numbers, then the job can be done but sometimes it is not possible to sort the data. Anyway let us try accomplish the task using function in excel formula.

>

The Microsoft Excel LARGE function returns the ‘n’th largest value from a set of values. The LARGE function is a built-in function in Excel and is categorized as a Statistical Function. 

Let us first discuss the function then we will discuss it with some examples.

Syntax

The syntax for the LARGE function in Microsoft Excel is...

=LARGE( array, k)

Parameters or Arguments

array

A range or array from which you want to return the nth largest value.

k

The position from the largest to return.

Returns

The LARGE function returns a numeric value.

Note : If ‘k’th value is larger than the number of values in array, the LARGE function will return the #NUM! error. If array is empty, the LARGE function will return the #NUM! error.

How to use LARGE function?

Excel LARGE function with criteria

As shown in the below picture there are 11 different numbers, if we need to get the second largest number we can write the excel formula like below.

=LARGE(C3:C13,2)

Here ‘C3:C13’ (i.e., ARRAY) defines the array and ‘2’ (i.e., ‘k’ th position) represents the position of the number which is the largest. 

Excel LARGE function with criteria

How to use LARGE function with ARRAY?

Excel LARGE function with multiple criteria

In the above example let suppose we need the sum of top three largest number, which is 844 + 764 + 740 = 2348. (Colored Blue, Red & Yellow)

Now there is a problem with LARGE function is that it accepts only one argument as the ‘k’th position.

Therefore, we need to pass an ARRAY in this parameter. 

What is an ARRAY?

ARRAY is a set of numbers or strings. If you want to know more about ARRAY, please click here.

We know to implement ARRAY in excl formula we need to press Ctrl+Shift+Enter simultaneously. The formula becomes 

{=SUM(LARGE(C3:C13,E4:E6))}

Now one obvious question will arise what are these curly braces doing in the beginning and at the end of the formula?

The answer to this question is, curly braces will appear whenever we declare an array in any excel formula, curly brace will start at the beginning of the formula and will end at the end of the formula.

Let us explore the above function step by step.

Excel LARGE function with multiple criteria

This excel formula is working in two steps, first the formula finds the top three largest numbers and the second part is summing up these numbers.

For more details please visit the discussion about ARRAY in Microsoft Excel.

Hope you have enjoyed this article.

Thank you for reading if you like this article, please share…. 

EXACT

In the previous articles, we have discussed about how to match rows, how to match one excel column with another excel column in one Excel Workbook and between two different Workbooks. We used VLOOKUP, HLOOKUP, INDEX MATCH MATCH, we used VLOOKUP with MATCH function and so on to get the exact matching of data column wise.

In this article, we will discuss how to compare one cell’s data with another cell’s data to be equal.

If both the two cells contain numbers, then it is easy to compare because you can subtract one number from another number in the next cell and if the output or result is greater than zero then there is mismatch between two cells but you cannot compare strings or texts using this technique. 

You might have noticed while working with a large data that sometimes the content or data of one cell seems to be same or identical or equal to another cell’s data but these are not equal which may be a reason that VLOOKUP or other function is not working!!! Let us have an example to understand this situation.

Suppose we have a name in a cell, Johnny Depp (A former World No.1 American professional tennis player) and in the next cell it is Johnnny Depp, the same name. In this two cells is difficult to verify the error that one ‘n’ is extra in the second cell. Now imagine the difficulty to identify the mismatch in an Excel sheet which contains hundreds of names!!!

There may be another situation, Johnny Depp in one cell and JoHnny Depp in the next cell, now if you compare these two cells, you will find that in the second name Johnny, ‘h’ is capitalized.

Therefore, we need some excel formula or function to identify this type of problems in an Excel table.

Microsoft Excel has provided us EXACT function which can do the task very easily.

What is Exact function in Excel?

The Microsoft Excel EXACT function compares two strings between two CELLS and returns TRUE if both values are the same. Otherwise, it will return FALSE.

Syntax

The syntax for the EXACT function in Microsoft Excel is very simple, type

=EXACT(text1, text2)

Parameters or Arguments

text1 and text2
The two string values to compare.

Returns

The EXACT function returns TRUE if the two strings are the same.
The EXACT function returns FALSE if the two strings are different.

Please Note : The EXACT function is case-sensitive when it compares the two strings. If you need to compare two strings or text irrespective of case sensitivity, you must use another function nested and a little trick. Let us give few examples on this subjects.

Example 1 :

How to use EXACT function to compare two cells ?

In the below image, I need to check whether two cells have same data or not.

The task is simple, just in the next column (here E column, column head is ‘EXACT Function’), type =EXACT(C3,D3).

If the result is TRUE then two cells data are same else if the output is FALSE these cells contains different texts.

EXACT Function in Microsoft Excel

Example 2 :

Now if you look carefully you can notice that although the names are same in few cells (highlighted yellow) but the result of EXACT Function is FALSE. This is because EXACT function is Case sensitive function, if the texts or strings in the two cells are not similar in Case, EXACT function will return FALSE.

How to override the case sensitivity of EXACT function?

In this point, you need to be a little tricky and the trick is convert both the strings in either upper case or lower case by using UPPER function or LOWER function.

The excel formula becomes, =EXACT(UPPER(C4),UPPER(D4))

EXACT Function Without Case Sensitivity in Microsoft Excel

Note : There is an easy method you can implement in Excel to compare two cells without considering case sensitivity just type ‘=’ sign and put the first cell’s reference and again type ‘=’ and type the second cells reference like the below image.

Excel formula to compare cell between two Columns

Hope you have enjoyed this article.

Thank you for reading if you like this article, please share….