Text Functions in Excel

text functions excel

Excel text functions add, extract, and edit text. Excel has over 30 text functions to edit, format, and clean data. It is so much more than number crunching.

Excel’s text functions include LOWER, UPPER, PROPER, CONCATENATE, TRIM, LEFT, RIGHT, and TEXT.  In fact, Excel is better for editing and data cleaning than Word.

Capitalization text functions

FunctionDescription
LOWERall letters lowercase
PROPERfirst letters uppercase
other letters lowercase
UPPERall letters uppercase

LOWER

Converts all letters in a text string to lowercase.

=LOWER(text)

  • Text – is the text you want to convert to lowercase. Characters that are not letters are not changed.

PROPER

Converts a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase.

=PROPER(text)

  • Text – is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing text to partially capitalize.

UPPER

Converts a text string to all uppercase letters.

=UPPER(text)

  • Text – is the text you want converted to uppercase, a reference or a text string.

Editing text functions

FunctionDescription
CLEANremoves nonprintable characters
TRIMremoves extra spaces, except
single spaces between words
LEFTextracts a given number
of characters from the left
LENcounts characters in a cell
MIDextracts from the middle, given a
starting point and number of characters
RIGHTextracts a given number
of characters from the right
TEXTconverts a value to text in
a specific number format
TEXTBEFOREreturns text before
delimiting characters
TEXTAFTERreturns text after
delimiting characters
TEXTSPLITsplits text into row and
columns based on delimiter
Editing Text Functions

CLEAN

The CLEAN function removes all nonprintable characters from a text string.

=CLEAN(text)

  • text – The text to search for nonprintable characters.

TRIM

The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text.

=TRIM (text)

  • text – The text from which to remove extra space.

LEFT

The LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT(“apple”,3) returns “app”.

=LEFT (text, [num_chars])

  • text – The text from which to extract characters.
  • num_chars – [optional] The number of characters to extract, starting on the left side of text. Default = 1.

LEN

Returns the number of characters in a text string. A cell containing John Smith would return a length of 10. A cell containing Nashville, TN returns a length of 13.

=LEN(text)

  • Text – is the text whose length you want to find. Spaces count as characters.

MID

The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID(“apple”,2,3) returns “ppl”.

=MID (text, start_num, num_chars)

  • text – The text to extract from.
  • start_num – The location of the first character to extract.
  • num_chars – The number of characters to extract.

The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, RIGHT(“apple”,3) returns “ple”.

=RIGHT (text, [num_chars])

  • text – The text from which to extract characters on the right.
  • num_chars – [optional] The number of characters to extract, starting on the right. Optional, default = 1.

TEXT

Converts a value to text in a specific number format.

=TEXT(value,format_text)

Value – is a number, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.

format_text – is a number format in text form from the Category box on the Number tab in the Format Cells dialog box (not General).

The TEXT function is useful to format text in many different ways. The following table shows examples.

TEXT function examples

FormulaDescription
=TEXT(1234.567,“$#,##0.00”)Currency with a thousands separator
and 2 decimals: $1,234.57
=TEXT(TODAY(),“MM/DD/YY”)Today’s date in MM/DD/YY
format: 10/29/19
=TEXT(TODAY(),“DDDD”)Today’s day of the week: Monday
=TEXT(NOW(),“H:MM AM/PM”)Current time: 1:29 PM
=TEXT(0.285,“0.0%”)Percentage: 28.5%
=TEXT(4.40 ,“# ?/?”)Fraction: 4 2/5
=TRIM(TEXT(0.75,“# ?/?”))Fraction: 3/4. Note this uses
the TRIM function to remove
the leading space with a decimal value.
=TEXT(12200000,“0.00E+00”)Scientific notation: 1.22E+07
=TEXT(1234567898,“[<=9999999]
###-####;(###) ###-####”)
Phone number: (123) 456–7898
=TEXT(1234,“0000000”)Add leading zeros (0): 0001234
Text Functions Examples

Join Text Functions

FunctionDescription
CONCATjoins values together,
can be a range of cells
CONCATENATEjoins up to 30 text items together
TEXTJOINjoins values with a delimiter,
can ignore empty cells
Joining Text Functions

CONCAT

The CONCAT function joins values supplied as references or constants. Unlike the CONCATENATE function (which CONCAT replaces), CONCAT allows you to supply a range of cells to join, in addition to individual cell references.

=CONCAT (text1, [text2], …)

  • text1 – First text value, cell reference, or range.
  • text2 – [optional] Second text value, cell reference, or range

CONCAT is the new function Microsoft released to replace CONCATENATE

CONCATENATE

The Excel CONCATENATE function joins up to 30 text items together and returns the result as text. The CONCAT function replaces CONCATENATE in newer versions of Excel.

=CONCATENATE (text1, text2, [text3], …)

  • text1 – The first text value to join together.
  • text2 – The second text value to join together.
  • text3 – [optional] The third text value to join together.

TEXTJOIN

The Excel TEXTJOIN function joins values with a given delimiter. Unlike the CONCAT function, TEXTJOIN allows you to supply a range of cells, and has a setting to ignore empty values.

=TEXTJOIN (delimiter, ignore_empty, text1, [text2], …)

  • delimiter – Separator between each text.
  • ignore_empty – Whether to ignore empty cells or not.
  • text1 – First text value or range.
  • text2 – [optional] Second text value or range.

Search Text Functions

FunctionDescription
FINDget the location of text in a string
(case sensitive and no wildcards)
REPLACEreplaces part of a text string with
text specified, given starting
number and number of characters
SEARCHget the location of text in a string
SUBSTITUTEfinds some text and replaces
it with some different text
Search Text Functions

FIND

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.

=FIND (find_text, within_text, [start_num])

  • find_text – The text to find.
  • within_text – The text to search within.
  • start_num – [optional] The starting position in the text to search. Optional, defaults to 1.

REPLACE

The REPLACE function replaces part of a text string with a different text string.

=REPLACE(old_text, start_num, num_chars, new_text)

  • Old_text: is text in which you want to replace some characters.
  • Start_num: is the position of the character in Old_text that you want to replace with New_text.
  • Num_chars: is the number of characters in Old_text that you want to replace.

The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards and is not case-sensitive.

=SEARCH (find_text, within_text, [start_num])

  • find_text – The text to find.
  • within_text – The text to search within.
  • start_num – [optional] Starting position in the text to search. Optional, defaults to 1.

SUBSTITUTE

Replaces existing text with new text in a text string.

=SUBSTITUTE(text,old_text,new_text,instance_num)

  • text: is the text or the reference to a cell containing text in which you want to substitute characters.
  • old_text: is the existing text you want to replace. It is case-sensitive.
  • new_text: is the text you want to replace old_text with.
  • instance_num: specifies which occurrence of old_text you want to replace. If omitted, every instance of old_text is replaced.

Date text functions

Excel has several text functions using dates, days, weeks, months, and years.

FunctionDescription
DATEreturns the serial number date
DATEVALUEconverts a text date into
the serial number date
TODAYcurrent date
NOWcurrent date and time
WEEKDAYgiven a date, returns a number 1–7
with the default where Sunday = 1
MONTHgiven a date, returns the month from 1–12
YEARgiven a date, returns the
year as a four digit number
YEARFRACreturns a decimal value representing
fractional years between two dates
ROMANconverts Arabic numerals
to Roman numerals
ARABICconverts Roman numerals
to Arabic numerals
Date Text Functions

DATE

Returns the Excel serial number date

=DATE(year,month,day)

  • Year – is a number from 1900 to 9999.
  • Month – is a number from 1 to 12 representing the month of the year.
  • Day – is a number from 1 to 31 representing the day of the month.

DATEVALUE

Converts a text date to an Excel serial date.

=DATEVALUE(date_text)

  • Date_text – text that represents a date in Excel date format, between 1/1/1900 and 12/31/9999.

TODAY

TODAY function returns the current date. TODAY is updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format. If you need the current date and time, use the NOW function.

=TODAY ()

The TODAY function requires no parameters but you must use parentheses. 

To enter the current date without a function, use ⌃ ; for macOS or Ctrl ; for Windows.

NOW

The Excel NOW function returns the current date and time. It is updated continuouslywhen a worksheet is changed or opened. The NOW function takes no arguments. You can format the value returned by NOW as a date, or as a date with time by applying a number format.

=NOW ()

The NOW function requires no parameters but you must use parentheses. 

To enter the current time without a function, use ⌘ ; for macOS or Ctrl Shift : for Windows.

WEEKDAY

The Excel WEEKDAY takes a date and returns a number between 1–7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. You can use the WEEKDAY function inside other formulas to check the day of week and react as needed. 

=WEEKDAY (serial_number, [return_type])

  • serial_number – The date for which you want to get the day of week.
  • return_type – [optional] A number representing day of week mapping scheme. Default is 1.

The available schemes are as follows:

  • 1: Numbers 1 (Sunday) through 7 (Saturday)) – default
  • 2: Numbers 1 (Monday) through 7 (Sunday)
  • 3: Numbers 0 (Monday) through 6 (Sunday)
  • 11: Numbers 1 (Monday) through 7 (Sunday)
  • 12: Numbers 1 (Tuesday) through 7 (Monday)
  • 13: Numbers 1 (Wednesday) through 7 (Tuesday)
  • 14: Numbers 1 (Thursday) through 7 (Wednesday)
  • 15: Numbers 1 (Friday) through 7 (Thursday)
  • 16: Numbers 1 (Saturday) through 7 (Friday)
  • 17: Numbers 1 (Sunday) through 7 (Saturday)

MONTH

The Excel MONTH function returns the month portion of a date as number between 1 to 12 when given a date. You can use the MONTH function to extract a month number from a date into a cell. You can also use the MONTH function to feed a month number to another formula, like the DATE function.

=MONTH (date)

  • date – any date

YEARFRAC

YEARFRAC function returns a decimal value that represents fractional years between two dates. You can use YEARFRAC for formulas that do things like calculating age in years using a birthdate.

=YEARFRAC (start_date, end_date, [basis])

  • start_date – The start date.
  • end_date – The end date.
  • basis – [optional] The type of day count basis to use.

The type of day count basis to use:

  • Basis 0: US (NASD) 30/360 – default
  • Basis 1: Actual/actual
  • Basis 2: Actual/360
  • Basis 3: Actual/365
  • Basis 4: European 30/360

YEAR

The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell. You can also use the YEAR function to extract and feed a year value into another formula, like the DATE function.

=YEAR (date)

ROMAN

Converts an Arabic numeral to Roman numeral. The Roman numeral is text.

=ROMAN(number, [form])

  • Number – the Arabic numeral you want to convert.
  • Form – the number or logic (0–4, TRUE, or FALSE) specifying the type of Roman numeral.
  • 0 or omitted – Classic
  • 1 – More concise
  • 2 – More concise
  • 3 – More concise
  • 4 – Simplified
  • TRUE – Classic
  • FALSE – Simplified

ARABIC

Converts a Roman numeral to an Arabic numeral. (Roman numerals are text.)

=ARABIC(text)

  • text – Roman numeral you want to convert


Scroll to Top