Excel Functions Quick Guide – Part 1 – Strings

Char

Returns a character based on its ASCII value.
=Char(100) returns d.

Code

Returns the ASCII value of the first letter in a cell.
=Code(“d”) returns 100.

Concatenate

Joins together between 2 and 30 strings.
=Concatenate(“d”, “o”) returns do.
Alternatively, use the ampersand operator; =”th”&”is” returns this.

Exact

Compares two strings and returns true if they match exactly, false otherwise.
=Exact(“do”,”Do”) returns false.

Find

Returns the first location of a substring within a string, from a given start point, case-sensitive. If the substring is not found, returns a #VALUE! error.
=Find(“o”, “do”) returns 2.
=Find(“a”, “do”) returns #VALUE!.
=Find(“O”, “do”) returns #VALUE!.
=Find(“o”, “do”, 3) returns #VALUE!.

Fixed

Returns a text representation of a number, rounded to a given number of decimal places, with or without commas. Compare to Value.
=Fixed(1234.56789,2,FALSE) returns 1234.56 (as text).
=Fixed(1234.56789,3,TRUE) returns 1,234.567 (as text).

Left

Returns a substring from a string, starting with the leftmost character and continuing for a given number of characters.
=Left(“this”,2) returns th.

Len

Returns the length of a given string.
=Len(“this”) returns 4.

Lower

Returns a given string with all letters converted to lowercase.
=Lower(“ThIs”) returns this.

Mid

Returns a substring from a string, starting from a given position, and continuing for a given number of characters.
=Mid(“do this”,4,2) returns th.

Proper

Returns a given string with the first letter of each word converted to uppercase, and all other letters to lowercase.
=Proper(“dO tHiS”) returns Do This.

Replace

Replaces characters in a string with a new string, beginning from a given point and continuing for a given number of characters.
=Replace(“do this”,6,2,”at”) returns do that.

Rept

Returns a string, repeated a given number of times.
=Rept(“this”,3) returns thisthisthis.

Right

Returns a substring from a string, starting with the rightmost character and continuing for a given number of characters.
=Right(“this”,2) returns is.

Search

Returns the first location of a substring within a string, from a given start point, not case-sensitive. If the substring is not found, returns a #VALUE! error.
=Search(“o”,”do”) returns 2.
=Search(“O”,”do”) returns 2.
=Search(“a”,”do”) returns #VALUE!.
=Search(“o”,”do”,3) returns #VALUE!.

Substitute

Replaces one substring with another, within a given string. You can replace all instances, or a particular one.
=Substitute(“don’t do that”,”do”,”ca”,1) returns can’t do that.
=Substitute(“don’t do that”,”do”,”ca”) returns can’t ca that.
=Substitute(“don’t do that”,”do”,”ca”,2) returns don’t ca that.

T

Returns the text from a given source.
=T(“this”) returns this.
=T(“100”) returns 100.
=T(100) returns nothing – no text, no error.
To elaborate, if you have a cell with the value 100 in it as text, the function T will return it. If you have a cell with the value 100 in it as a number, the function T will not recognise it.

Text

Returns a given value as text in a given format.
=Text(1234.56789,”0″) returns 1235.
=Text(1234.56789,”0.00″) returns 1234.57.
=Text(1234.56789,”0,000.0″) returns 1,234.6.
=Text(“31/01/2012″,”mmm dd, yy”) returns Jan 31, 12.
=Text(“31/01/2012″,”yy-mm-dd”) returns 12-01-31.
=Text(“31/01/2012″,”dddd, mmmm dd yyyy”) returns Tuesday, January 31 2012.

Trim

Returns a string with extra spaces removed.
=Trim(”      do        this         “) returns do this.

Upper

Returns a given string with all letters converted to uppercase.
=Upper(“ThIs”) returns THIS.

Value

Returns a text representation of a number as an actual number. Compare to Fixed.
=Value(“34”) returns 34 (not as text).

Leave a Reply