Tag

STRING/TEXT FUNCTIONS

Browsing

The XMATCH function executes lookup and returns a position in vertical or horizontal ranges, it searches for a specified item in an array or range of cells and then returns the item’s relative position. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches. 

Purpose of Excel XMATCH Function

Searches for a specified item in an array or range of cells.

Return value

Returns the relative position of the specified.

Syntax

=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])

Arguments

ArgumentsDescriptionRemarks
Lookup ValueRefer a value to be looked upMandatory
Lookup ArrayArray or Range to searchMandatory
Match Mode0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.Optional
Search Mode1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.Optional

Usage notes

  • XMATCH can perform lookups in vertical or horizontal ranges, and is meant to be a more flexible and powerful successor to the MATCH function.

Availability

Microsoft 365

The VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric values.

Purpose of Excel VALUE Function

Convert text to a number.

Return value

A numeric VALUE.

Syntax

=VALUE (text)

Arguments

ArgumentsDescriptionRemarks
textthe text value to convert to a numberMandatory

Usage notes

  • Use this function to convert text to a numeric value.
  • This function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value.
  • Normally, Excel automatically converts text to numeric values as needed, so this function is not needed.
  • Microsoft states that this function is provided for compatibility with other spreadsheet programs.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The UNICODE function returns a number (code point) corresponding to a Unicode character in decimal format. this function is computing standard for the unified encoding, representation, and handling of text in most of the world’s writing systems.

Purpose of Excel UNICODE Function

Get number from Unicode character.

Return value

Unicode code point in decimal.

Syntax

=UNICODE (text)

Arguments

ArgumentsDescriptionRemarks
textUnicode character to convert to numberMandatory

Usage notes

  • This function performs the opposite conversion, returning the code point for a given Unicode character.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The UNICHAR function returns a Unicode character based on a given number.

Purpose of Excel UNICHAR Function

Get Unicode character by number.

Return value

Unicode character.

Syntax

=UNICHAR (number)

Arguments

ArgumentsDescriptionRemarks
numberCode point for a Unicode character in decimalMandatory

Usage notes

  • If number is out-of-range, this function returns #VALUE!.
  • If number is not a recognized number, UNICHAR .

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The TEXT function returns a number in a given number format, as text. You can use this function to embed formatted numbers inside the text.

Purpose of Excel TEXT Function

Convert a number to TEXT in a number format.

Return value

A number as TEXT in the given format.

Syntax

=TEXT (value, format_text)

Arguments

ArgumentsDescriptionRemarks
valueThe number to convertMandatory
format_textThe number format to use Mandatory

Usage notes

  • This function can be used with custom number formats.
  • Format_text must appear in double quotation marks.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

Example

TEXT Function

Above is a simple example of seller details where the date is n the month format, But we need the date of sales it can be done by changing the date format to “dd-mm-yyyy” TEXT function is helpful to do this.
Formula used: =TEXT(B3,”dd-mm-yyyy”)
By executing the above formula the month format will be changed to date format, which gives us the date of sales.

Suggested reading: 20 Excel Functions Useful For Finance Professionals

The T function returns text when given a text value and an empty string (“”) for numbers, dates, and the logical values TRUE and FALSE. The T function is provided for compatibility with other spreadsheet programs and is generally not required.

Purpose of Excel T Function

Filter text values only.

Return value

Value as text when text.

Syntax

=T(value)

Arguments

ArgumentsDescriptionRemarks
valueThe value to return as textMandatory

Usage notes

  • This function returns text when given a text value and an empty string (“”) for numbers, dates, and the logical values TRUE and FALSE.
  • In most cases, using this function is unnecessary, because Excel automatically converts values when needed.
  • This function is provided for compatibility with other spreadsheet programs.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The SUBSTITUTE function replaces text in a given string by matching. SUBSTITUTE is case-sensitive and does not support wildcards.

Purpose of Excel SUBSTITUTE Function

Replace text based on content.

Return value

The processed text.

Syntax

=SUBSTITUTE (text, old_text, new_text, [instance])

Arguments

ArgumentsDescriptionRemarks
textThe text to changeMandatory
old_textThe text to replace Mandatory
new_textThe text to replace withMandatory
instanceThe instance to replace. If not supplied, all instances are replacedOptional

Usage notes

  • SUBSTITUTE finds and replaces old_text with new_text in a text string.
  • Instance limits SUBSTITUTE replacement to one particular instance of old_text.
  • Use SUBSTITUTE to replace text based on content.
  • Use the REPLACE function to replace text based on its location.
  • SUBSTITUTE is case-sensitive and does not support wildcards.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The 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.

Purpose of Excel SEARCH Function

Get the location of text in a string.

Return value

A number representing the location of find_text.

Syntax

=SEARCH (find_text, within_text, [start_num])

Arguments

ArgumentsDescriptionRemarks
find_textThe text to findMandatory
within_textThe text to search within Mandatory
start_numStarting position in the text to search. Optional, defaults to 1.Optional

Usage notes

  • Use the SEARCH function to get 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 the use of wildcards, and is not case-sensitive.
  • SEARCH allows the wildcard characters question mark (?) and asterisk (*), in find_text.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The REPT function repeats characters a given number of times.

Purpose of Excel REPT Function

Repeat text as specified.

Return value

The repeated text.

Syntax

=REPT (text, number_times)

Arguments

ArgumentsDescriptionRemarks
textThe text to repeatMandatory
number_timesThe number of times to repeat textOptional

Usage notes

  • Use this function to repeat text a given number of times.
  • This can be useful if you want to fill a cell, or pad values to a certain length.
  • It’s possible to build a basic histogram chart using REPT, by translating values directly into a certain number of (repeated) characters.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The REPLACE function replaces characters specified by location in a given text string with another text string.

Purpose of Excel REPLACE Function

REPLACE text based on location.

Return value

The altered text.

Syntax

=REPLACE (old_text, start_num, num_chars, new_text)

Arguments

ArgumentsDescriptionRemarks
old_texTThe text to replaceMandatory
The text to replaceThe starting location in the text to searchOptional
num_charsThe number of characters to replaceOptional
new_textThe text to replace old_text withOptional

Usage notes

  • This function is useful for replacing text at a known location in a given string.

Availability

Since 2007 for Windows.
Since 2011 for Mac.