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