The Most Important Excel Functions for Analytics – Part 3/3

The final installment of ‘The Most Important Excel Functions For Analytics’ covers some common text manipulation techniques to pull apart your data. There will often be times when you will need to extract or merge data. For example you may need to extract a part of a date, the first characters of a product code or join together information within multiple cells to form a unique key.

However, before we start Part 3, a quick Excel joke:

PART 3 – PULLING APART THE DATA

This section will cover the four most useful functions for extracting or combining data.

  • LEN
  • CONCATENATE
  • LEFT,RIGHT,MID
  • DAY/MONTH/WEEKNUM

7. LEN

Formula:

LEN(text)

Why do I care?:

LEN  is useful in checking ID codes. Often a product ID or a tracking code has a fixed number of characters, LEN is a good way to find errors in the data.

What is an example?:

A UPS tracking code has 18 characters. To check whether a tracking code is incomplete or an error, you may check that there are at least 18 characters. In the below example we see that the length of cell B6 is only 9 characters, suggesting there is an error in the tracking code.

Length of tracking numbers

Insights:

  • LEN counts all characters in a string, including white spaces before or after the main text. To remove the white spaces before or after the string, you can use the TRIM() function

8. CONCATENATE

Formula:

CONCAT(text1, [text2], …)    #Excel 2016

CONCATENATE(text1, [text2], …)   #Earlier Excel

Why do I care?:

When cleaning data, you will find that often you do not have the structure you are looking for. You will need to combine data together to form new columns. Concatenation joins values together to make a longer string. This is very helpful when joining address parts, displaying dates and text in the desired format and when creating a complex VLOOKUP that relies on multiple columns.

Note that Excel 2016 changed the function from CONCATENATE () to CONCAT().

What is an example?:

Suppose you want to join address fields together. You have a street number, a street name and a state. There are 2 approaches you can use. The first involves the CONCAT function, note that after each entry, you add a white space ” ” to prevent the strings being forced together without any spaces. The second method is to use the & character as per the second row.

Concatenate function

Insights:

  • The result of a concatenation is always a string
  • You can concatenate up to 255 strings
  • A useful function that is often used in concatenation is text(), this converts a value into a txt value with an assigned format. For example to convert a date into a text format you may use =TEXT( A1, “dd/mm/yyyy” )

9. LEFT,RIGHT,MID

Formula:

LEFT(text_string, char_numbers)

RIGHT(text_string, char_numbers)

MID(text_string, start_number, char_numbers)

Why do I care?:

The LEFT/RIGHT/MID functions are used to deconstruct a string. Common reasons to use these functions are to remove post codes from an address, extract names from emails, conver text to numbers and to break out a concatenation into its original forms.

What is an example?:

Say you have a product code that is a combination of gender, retail season and item code. You can create three new columns and use the left function to extract the gender, the mid function to obtain the retail season and the right function for the item details.

Use LEFT/RIGHT/MID to break up a string

Insights:

  • Number formatting will not be extracted when these functions are run e.g. $ sign for an integer such as $20.30
  • Another way to break up a string is to use text to column wizard, see the attached link for more Text to column

10. DAY/MONTH/WEEKNUM 

Formula:

DAY(serial_number)

MONTH(serial_number)

WEEKNUM(serial_number, [return_type])

Why do I care?:

Often you will be dealing with dates in your data set and you want to understand seasonality. By extracting the day of the month, the month of the year or the week number, you will be able to aggregate and chart the different patterns over time.

What is an example?:

Taking a series of dates and extract the day of the month, the month number and the week number. Note that weeknum’s default return type is to commence the week on Sunday, this option can be changed to 2 if you want the week to commence on Monday.

Date functions

Insights:

  • You can add or subtract dates by using the DATE function such as =DATE(YEAR(A3) + X years, MONTH(A3) + Y months, DAY(A3) + Z days)
  • The text function can be used to extract a part of a date as a string e.g. TEXT(A3,”MMM”) would return FEB in our example

Well done, you have now mastered the most useful Excel functions for Business Analytics. To recap, we covered Summarizing Data in Part 1, Customizing Data Part 2 and Pulling Apart Data in Part 3. In the next blog, I’ll go through the most useful Excel shortcut keys to help boost your productivity.

Share on LinkedInEmail this to someoneTweet about this on TwitterShare on FacebookPrint this page

Leave a Reply

Your email address will not be published. Required fields are marked *

*