12 Excel functions everyone should know (especially marketers)

1. Split text with functions Left, Right and Find.Spec

A common problem: you receive an Excel file, but both first name and last name are in one cell. No problem thanks to the following functions:

• Feature Links
• Function Right
• Function Length
• Function Find.Spec

Example:

• Define first name: =LINKS(B3;VIND.SPEC(" ";B3;1))
• Determine last name: =RIGHT(B3;LENGTH(B3)-LENGTH(C3))

You can also use these functions to extract domain name from the e-mail address, for example, or to split zip code and municipality, for example, when they are in one cell.

2. Convert text to capital letters

Or how about this one: a colleague sends you an Excel file where some of the text is in capitals (all caps). We can easily solve this too:

• Function Initial letters

Example:

• =BEGINNING LETTERS(C3)

3. Removing spaces in Excel

Possibly also recognizable: certain text contains an annoying space at the front. How can this be solved without retyping the entire text?

• Function Blanks.Delete

Example:

• =BLANKS.DELETE(C3)

4. Merge text from various cells

The following function is not actually a specific function in Excel. Nevertheless, it is a tip that can save you a lot of time:

• Use of & sign i.c.w. " "

Example:

• =C3&""&D3

5. Text to columns (useful for CSV files).

As a marketer, you undoubtedly work with CSV files from time to time. You can convert these to a table via 'Data' > 'Retrieve and transform data' > 'From Text/CSV'. Or you can use the function 'Text to columns'. Also useful to separate other texts with a clear separator.

• Function: Text to columns

6. Removing duplicate values in Excel

Did you know there is a function in Excel to detect and remove duplicate values? Tip: Of course, it's best to search here for unique values such as an email address or ID number, for example.

• Function: Delete duplicate values

7. Horizontal search

No doubt you have ever had to do an exercise in school involving horizontal and vertical search? Do you also always confuse the two? In Horizontal Search, Excel is going to try to match the specified value horizontally, and then - hence the confusion often - retrieve a certain value vertically.

In the example below, I extract the years of service and date of retirement from a table for the specified staff members:

• Function: Horizontal Search
• Function: Fix a cell or row using $ character

Example:

• Search for 'Years of Service': =HORIZ.SEARCH($B12;$B$2:$G$6;3;0)
• Search for 'Date retired': =HORIZ.SEARCH($B12;$B$2:$G$6;3;0)

8. Vertical search

In vertical search, Excel is going to match a specified value vertically and then retrieve value horizontally in a given cell. Below I give an example of searching for a congregation of certain people in a table:

• Function:Vertical Search
• Function: Fix a cell or row using $ character

Example:

• =VERT.ZOEKEN($C14;$B$3:$J$10;9;0)

9. Function As

Perhaps the function I use most often in Excel is the well-known if function. Useful for incorporating controls, for example:

• Function: As

Example:

• =ALS($B4<4; "Junior"; "Senior")

10. Function Number If

The "Number If" function is definitely one to take good note of as a marketer. It sometimes comes in handy, for example, when you need to compare two Excel files. In order to detect duplicate values. Or to summarize a large database.

In the example below, I used the 'Number of If' function to determine the number of Junior vs Senior profiles in a company:

• Function:Number as

Example:

• =AANTAL.ALS($H$12:$H$14;$D19)

11. Function Search and Replace

The following function is a classic: 'Search and replace' or CTRL+H. Ideal for quickly replacing certain values in a database with something else:

• Function: Search and replace

12. Moving columns quickly in Excel

Perhaps you - like most - also move columns via cut and paste? Did you know that by holding the shift key you can move columns quickly?

• Function: Move columns by shift key