Other text functions

There is a matching Excel file that you can use to follow along and practice, which you can download from here.

Substitute

SUBSTITUTE(celladdress, oldtext, newtext): Allows you to mass replace (or elimination) of a specific word or phrase in a column. For example, I have a list of school districts and the names of the schools all end with “public school district”. But I want to strip that off.

here's the formula in the picture to the left:
=SUBSTITUTE( H5, "PUBLIC SCHOOL DISTRICT", "" )

In this example, I’m leaving the “newtext” part of the formula blank because I don’t want to replace the phrase with something else. If you wanted to change it — perhaps you want it to say, “Schools” — then you could put that within that last set of quotes.

=SUBSTITUTE( H5, "PUBLIC SCHOOL DISTRICT", "SCHOOLS" )

The function is very specific. For example it won’t replace the phrase “PUBLIC SCHOOL DIST” because it’s not an exact match.

Exact()

EXACT(celladdress1, celladdress2): Compares two strings to see whether they are identical. This is great for if you are trying to line up two sets of lists. Let’s say each contains the 50 states, so you want to align them by the name of the state (which appears in both lists). It returns FALSE if the two items are not identical.

LEN(celladdress): Returns the length in number of bytes. For example, if you have a cell with "Austin, TX" in the cell, this function would return the number 10, while a cell with just "TX" would return the number 2. It counts all the digits, plus any spaces.

PROPER(celladdress): Converts the data in the cell to proper case.

LOWER(celladdress): Converts the data to lowercase

UPPER(celladdress): Converts the data to uppercase

Page updated

Google Sites

Report abuse