Excel comes with many built-in text functions that help extract information from text columns and standardise data. For example, performing string manipulation on thousands of rows manually is practically impossible. In such cases, Excel’s text functions are very convenient.
In the following video, Vivek will demonstrate how to use some of the Excel text functions.
Here is a summary of what you learnt in the above demonstration:
-
LEFT function: Returns the specified number of characters from the left of a string.
Syntax:LEFT(text, [num_chars])
[num_chars] is optional and specifies how many characters to extract from the left. -
SEARCH function: Returns the position of a specified character or substring within a string. SEARCH is not case-sensitive.
Syntax:SEARCH(search_text, within_text, [start_num])
For case-sensitive searches, use FIND, which has similar syntax. -
RIGHT function: Similar to LEFT but extracts characters from the right end of a string.
Syntax:RIGHT(text, [num_chars])
[num_chars] is optional and specifies how many characters to extract from the right.
For example, the logistics_provider_id "Ship2Home Logistics Unit" and "Ship2Home delivery" represent different subsidiaries of the same provider. To compare FastWheels with Ship2Home, we extract the first name using:
LEFT(E2, SEARCH(" ", E2)-1).
To extract the subsidiary, we use: RIGHT(E2, N2-LEN(L2)).
Using these text functions, you can extract the first names easily. In the next video, you will learn additional text functions like TRIM, PROPER, and CONCAT.
Let’s have a recap of what you learnt in the video above:
-
TRIM function: Removes extra spaces at the beginning, end, or middle of a string.
Syntax:TRIM(text) -
PROPER function: Converts a string to proper case. The first letter of each word is uppercase, and the rest are lowercase.
Syntax:PROPER(text) -
CONCAT function: Joins multiple strings together.
Syntax:CONCAT(text1, [text2, text3, ..., textn])
In the demonstration, we used TRIM to remove leading spaces from subsidiary names, PROPER to convert them to proper case, and CONCAT to create a column called "shipping routes" by combining origin and destination cities.
To explore more text functions in Excel, refer to the link: Text functions.
Now that you have a basic understanding of text functions, attempt the following questions using the Superstores_data dataset.
Next, you will learn about Statistical Functions.