How To Use MS Excel Text and String Functions
Get Information From Strings Using Microsoft XL
Jul 22, 2009
Excel offers at least 23 functions that allow the user to manipulate, or extract useful information from text. Knowing how to handle the most useful of these functions gives the Excel user powerful tools to save time, with very little effort. In all of the examples shown, literal values like "12345", and cell references like A1, may be used in the functions.
How To Extract Parts of Text from A String in Excel – LEFT, RIGHT, MID
There are three main functions used to do this, called LEFT, RIGHT and MID. If the text “MN 12345” represents a ZIP Code, then the U.S. state is indicated by the first two digits, and the area or street is represented by the last five digits. If a ZIP code is held in cell A1 of an Excel worksheet, then the function to obtain the state code is:
=LEFT(A1,2)
i.e. Use the string in cell A1, and take the leftmost two digits, “MN”. Similarly,
=RIGHT(A1,5)
… will return the string (not a number) “12345”.
To find the middle of a string, the “MID” function is used. Three values must be provided to the function – the text to be used, the start position, and the length of text to be returned. For example, to find the first two number digits in “MN 12345” used in the previous examples, the function is:
=MID(A1,4,2)
Note that there is a space between MN and 12345, so the start position is 4, not 3.
How To Find Out Information About A Text Field – LEN, VALUE, FIND
Three useful functions to find out about a string are LEN, VALUE, and FIND.
LEN returns the length of the text in a cell, including spaces.
=LEN(A1)
returns 8, if the cell contains “MN 12345”.
VALUE converts the numeric part of a string into a number. For example,
=RIGHT(“MN 12345”,5) returns the text “12345”. If the user wants the number 12,345 then the following will work:
=VALUE(RIGHT(“MN 12345”,5))
The FIND function is useful to determine the location of a character in a string. To check where the digit “4” is in “MN 12345”,starting in position 1, the function is
=FIND(“4”,”MN 12345”,1)
These functions apply when the cell contents are in the form of text, but will not work in date and time fields because Excel stores dates and times as a number.
How To Join and Change Strings – CONCATENATE, SUBSTITUTE, TEXT
To join two strings together, the CONCATENATE function is used. To join “MN” and “12345”, the command is
=CONCATENATE(“MN”,”12345”)
There will be no space between the two strings, unless it is deliberately included:
=CONCATENATE(“MN”, “ “, “12345”)
To change several characters at once in a string, the SUBSTITUTE function may be used. For example, the function needed to change “ABC-123-XYZ” to “ABC 123 XYZ” is
=SUBSTITUTE(“”ABC-123-XYZ”,”-“,” “)
If only the first two dashes need to be changed, then the function changes to
=SUBSTITUTE(“”ABC-123-XYZ”,”-“,” “,2)
To change numbers into text format, the TEXT command is available. To change the number 12,345 into text, with 3 decimal places, the function is
=TEXT(“12345”,”0.000”)
Summary of Excel Text Functions
There are at least 23 functions to handle text available in Excel. A widely used, and very useful, subset is described here that covers most types of string manipulation. More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.
 |
