Excel: text-based function

Intro to basic text-based functions in excel

This is a short intro to my background. I'm an electronics and telecommunications engineer. I started my web development journey last year. Now i have decided to switch to data analysis. This is the beginning of my data analysis journey.

  1. FIND

This function returns the position of the given text. This is a case sensitive function. It can be used to find a particular text where the case of the text is important.

eg.

2. SEARCH

This function also returns the position of the given text but this function is not case sensitive. It will return the first occurrence of the text ignoring the case of the text. It can be used to search for a text where case is not important.

eg.

3. UPPER/LOWER

This function returns the text converted into upper/lower case

eg.

4. LEFT/RIGHT

This function takes text, truncates it the provided length. The function trims from left/ right and returns the new text. We can use this function to remove unwanted and clean the data.

eg.

5. LEN

This function gives the length of the text. White space is also counted as a character and it is counted in len function.

eg.

6. REPT

This function repeats the text given number of times

eg.

7. REPLACE

This function replaces the old text with new text. This function in not case sensitive meaning it will replace the text ignoring the old text’s case. The function take starting position and number of characters to replace.

eg.

8. SUBSTITUTE

This function is same as REPLACE function except this function is case sensitive

eg.

9. TRIM

This function removes the extra white spaces. It ignores a space between two words and remove the other spaces. It can be use to clean the data.

eg.

10. TEXT

This function converts any form of value into the format the required format. Some formats are date, time, ratio, percentage, etc. This function can also be used to add leading zeros.

eg.

11. PROPER

This function converts the text into proper text meaning it converts the first character of the word into upper case. This function can be used where we have to store names.

eg.

12. CONCATENATE

This function concatenates two or more text into a single text. This function can be used where we have different columns of first name and last name.

eg.

If I made any mistake or have missed any point please let me know. This is the time I'm sharing my learning. Hope for your support.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Kiran

Data Analyst trying to write and share as much as possible