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 […]
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 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: […]
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). .
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
Join Strings | Left | Right | Mid | Len | Find | Substitute Excel has many functions to offer when it comes to manipulating text strings. Join Strings To join strings, use the & operator. Note: to insert a space, use ” “ Left To extract the leftmost characters from a string, use the […]
