Digital World Faisalabad: Text Functions

Showing posts with label Text Functions. Show all posts

Saturday, 1 July 2017

Concatenate Strings

This example illustrates four different ways to concatenate (join) strings in Excel.

1. Simply use the & operator to join strings.

Note: to insert a space, use " "

2. The CONCATENATE function produces the exact same result.

3a. The CONCAT function in Excel 2016 produces the exact same result.

3b. The CONCAT function can also join a range of strings. If you don't need a delimiter (space, comma, dash, etc.) this can be useful.

4a. The CONCAT function cannot ignore empty cells. Take a look at all the extra spaces in column E below if you drag the function in cell E2 down to cell E11.

4b. The beauty of the TEXTJOIN function in Excel 2016 is that it can ignore empty cells (if the second argument is set to TRUE).

Note: the TEXTJOIN function joins a range of strings using a delimiter (first argument)..

Substitute vs Replace

This example shows the difference between the SUBSTITUTE function and the REPLACE function.

1a. If you know the text to be replaced, use the SUBSTITUTE function.

1b. The SUBSTITUTE function has a 4th optional argument. You can use this argument to indicate which occurrence you want to substitute.

2. If you know the position of the text to be replaced, use the REPLACE function.

Note: started at position 1 with length 3..

The FIND function and the SEARCH function are very similar to each other. This example shows the difference.

1. To find the position of a substring in a string, use the FIND function. FIND is case-sensitive.

2. To find the position of a substring in a string, use the SEARCH function. SEARCH is case-insensitive.

Note: string "excel" found at position 11. Even though it's actually the string "Excel"

3. The SEARCH function is more versatile. You can use wildcard characters when you use this function.

Note: A question mark (?) matches exactly one character. An asterisk (*) matches a series of zero or more characters.

4. One other interesting point to mention about the FIND and the SEARCH function is that they have a 3rd optional argument. You can use this argument to indicate the position, counting from the left, at which you want to start searching.

Note: string "o" found at position 5.

Note: string "o" found at position 7 (started searching at position 6)..

Compare Text

This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive.

1. Use the EXACT function (case-sensitive).

2. Use the formula =A1=B1 (case-insensitive).

Remove Unwanted Characters

The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. The CLEAN function removes nonprintable characters from a string.

1. For example, cell A1 below contains the string " Excel Easy "

Note: the TRIM function returns the string "Excel Easy".

2. For example, cell A1 below contains a nonprintable character.

Note: the CLEAN function removes this nonprintable character. Text imported from other applications may contain nonprintable characters..

Lower/Upper Case

This example teaches you how to convert a text string to lower, upper or proper case in Excel.

1. Use the LOWER function to convert all letters in a text string to lowercase.

2. Use the UPPER function to convert all letters in a text string to uppercase.

3. Use the PROPER function to convert a text string to proper case. That is, the first letter in each word in uppercase, and all other letters in lowercase.

Text to Columns

To separate the contents of one Excel cell into separate columns, you can use the 'Convert Text to Columns Wizard'. For example, when you want to separate a list of full names into last and first names.

1. Select the range with full names.

2. On the Data tab, in the Data Tools group, click Text to Columns.

The following dialog box appears.

3. Choose Delimited and click Next.

4. Clear all the check boxes under Delimiters except for the Comma and Space check box.

5. Click Finish.

Note: This example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.

Result:

Number of Words

This example describes how to count the number of words in a cell.

1a. The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed.

1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function.

2a. The SUBSTITUTE function replaces existing text with new text in a text string. We use the SUBSTITUTE function to get the string without spaces.

2b. To get the length of the string without spaces, we combine the LEN and SUBSTITUTE function.

3. Now comes the simple trick. To get the number of words, we subtract the length of the string without spaces (10) from the length of the string with normal spaces (12) and add 1.

Number of Instances

This example describes how to count the number of instances of text (or a number) in a cell.

1. Use the LEN functon to get the length of the string (25 characters, including spaces).

2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,"")) equals 13 (the length of the string without the words dog). If we subtract this number from 25, we get the length of the dog instances (25-13=12).

3. Dividing this number by the length of the word dog (3), gives us the dog instances (12/3=4).

Separate Strings

This example teaches you how to separate strings in Excel.

The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9.

1. To get the first name, use the formula below.

Explanation: to find the position of the comma, use the FIND function (position 6). To get the length of a string, use the LEN function (11 characters). =RIGHT(A2,LEN(A2)-FIND(",",A2)-1) reduces to =RIGHT(A2,11-6-1). =RIGHT(A2,4) extracts the 4 rightmost characters and gives the desired result (Mike).

2. To get the last name, use the following formula.

Explanation: to find the position of the comma, use the FIND function (position 6). =LEFT(A2,FIND(",", A2)-1) reduces to =LEFT(A2,6-1). =LEFT(A2,5) extracts the 5 leftmost characters and gives the desired result (Smith).

3. Select the range B2:C2 and drag it down.

Text Functions

Note: to insert a space, use " "

Left

To extract the leftmost characters from a string, use the LEFT function.

Right

To extract the rightmost characters from a string, use the RIGHT function.

Mid

To extract a substring, starting in the middle of a string, use the MID function.

Note: started at position 5 (p) with length 3.

Len

To get the length of a string, use the LEN function.

Note: space (position 8) included!

Find

To find the position of a substring in a string, use the FIND function.

Note: string "am" found at position 3.

Substitute

To replace existing text with new text in a string, use the SUBSTITUTE function.

DW Faisalabad New Version