Text Functions | Page 2 of 2

Counting Text Columns like Employee Names in Excel

July 11, 2022October 2, 2012 by Vinai Prakash

Excel is great and summing things up. And then count the number of rows easily. You can watch the step by step video on how to use simple functions in Excel.

In fact, most versions of Excel can even show the SUM, COUNT of a block of cells, in the bottom status bar automatically.

However, there is an issue, if you want to count number of employees, or number of departments, or number of students…

Text values, by default can not be summed by the SUM function, and can not be COUNTED by the Count function in Excel

That’s because Excel is good at adding and counting numbers, and not Text. If you try, you’ll get a ZERO.

=COUNT(Employee_Names) will result in a ZERO answer.

Fortunately, the workaround is pretty simple, straight forward and easy to use in Microsoft Excel. And it counts text values with ease.

Instead of using the COUNT function, you can use the COUNTA function of Excel. COUNTA is to count Textual data.

So if you have characters, alphabets and number combinations (like employee numbers), you can count such text items using the COUNTA function of Excel.

This COUNTA Function works in all the versions of Excel – from Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and even Microsoft Office Excel 365 (the cloud version.)

So to count the number of employees, you only need to say =COUNTA(A5:A15).

And this nifty function will count the number of employees promptly. COUNTA is to count Alphanumeric values – text, strings… or even numbers.

Try it out, and let me know if this helps you… 🙂

You may find these resources useful:

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

P.S. – Are you an Expert in using Excel ? Try solving this – find a way to find duplicates in Excel 2010 quickly. And post your solution. We are looking for simple and innovative ways to do so…

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Excel Tutorial – Combine Multiple Text Columns Into a Single Column

July 11, 2022September 20, 2012 by Vinai Prakash

Some of the attendees at my Excel Boot Camp trainings have been thrilled to find that there’s this nifty function, which can combine many text columns into a single column.

Let’s say you have a column with First Name, and a separate column for Last Name, and you want to combine them to form the full name. How would you do it?

I have recorded a short video on how to achieve this, using 2 different methods. Check it out below:

Let me know if this works for you. And if you have any other method to achieve this, I’ll be glad to know it…

And check out my other Microsoft Excel Tips & Tricks videos on YouTube.

Cheers,
Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Extracting Digits from Serial Numbers in Excel

December 3, 2023June 14, 2012 by Vinai Prakash

What do you make of 12CNGY561RR9806?

Chances are that you’d be thinking this some kind of a joke… Well, it is a code, a serial number. If you open your Toaster, Phone, TV, Laptop or Tablet, you’d find a similar looking, equally difficult to read serial number.

You may be wondering – Why do they make it so difficult? And what is the purpose?

Well, these are serial numbers. Not for humans, more for computers and geeks. If you happen to take your product to the service centre, the bar code scanner can make sense of this gobbledegook right away!

To make sense, you need to be able to extract certain bits and pieces from this serial number, and analyze them separately.

For example, the first two characters (12) may mean the year of manufacture – 2012.

The next 2 characters (CN) may mean the country of manufacture- China.

The next 4 characters (CNGY) may mean the product code. Similarly, the last 4 digits (9806) may be the runing serial number of the product.

From such numbers, it is easy to find out the country, batch, make, product, and date of manufacture quite quickly.

But imagine staring a such numbers in an Excel file, and be able to quickly filter, find and select numbers belonging to a specific year, country or Product family.

Well, it can easily be done, using special in built functions in Excel – be it Microsoft Excel 2003, 2007 or Excel 2010 or even Microsoft Excel 2013.

See this Example Excel file for trying it yourself.

To solve this problem, we will use Excel’s inbuilt Text Functions – Left, Right and Mid.

The Left function will extract any characters from the left of a string. LEFT(‘ABCDEF’, 2) will extract AB.

The Right function will extract any number of characters you need, from the right of a string. RIGHT(‘ABCDEF’, 3) will extract DEF.

And the Mid function will extract any characters from the middle of a string. You just have to specify the starting number, and the number of digits required.

So, MID(‘ABCDEFGH’, 3, 2) begins to extract from the Third character, and extracts 2 characters. Therefore, the characters, “CD” get extracted easily.

Using these 3 simple functions, we can extract any digit, or character from any string in Excel. And this feature has been in Excel since ages…

Enjoy, and all the best!

Do post some comments if this article helped you!

Additional Resources:

Cheers,
Vinai Prakash
Founder & Editor, ExcelChamp.Net

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Are you facing any problem in using Excel? Any Question?

Are you facing any problem in using Excel? Any Question?

Are you facing any problem in using Excel? Any Question?

Additional Resources for Learning Excel & Become A Pro