Manipulating Text Strings in EXCEL

An issue that we frequently come across with our clients is users that are a little overwhelmed when they need to rearrange text strings differently than from the way that the text string was imported from a source system. As long as the text string is delimited, such as with a coma, asterisk, blank space, or colon we can use Excel’s different text functions to get the text string parsed as required. We can break the full name into first name, middle name and last name or any other forms of manipulations quite comfortably and quite DYNAMICALLY if we know different formulas which can manipulate text data

There are five primary text functions that users need to learn, which are LEFT(), RIGHT(), FIND(), LEN() and MID().

LEFT() – This has only two possible parameters: LEFT(source, # characters). The source is the text cell to be parsed, and the # characters are the number of characters we want returned beginning from the left most character

RIGHT() – This works exactly the same as the LEFT(), just beginning from the opposite side of the text string

FIND() – Like the previous two functions, this function only requires two parameters: FIND(character(s) to be found, source string). The first parameter needs to be enclosed with double quotes and represents the string to be found. The second parameter is the cell address of the text string to be searched. The FIND() function will return the position number, within our string, of the first occurrence of the character(s) being sought

LEN() – The LEN() function returns the length of the referenced text string

MID() – The MID function has three required parameters: MID(source, starting position, number of characters). The MID() function will extract a number of characters from within a text string