• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Excel Text Function with Examples

Excel Text Function with Examples

The Excel TEXT function is used to convert a numeric value to text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.

Excel TEXT Function Anatomy

The TEXT function in Excel requires two arguments – the text that needs formatting, and the format you want to use.

=TEXT(value, format_text)

It is very useful for displaying numbers that are used in text strings still in their number format. Examples of this include when using summary text on reports or in chart labels such as “Total sales this quarter: £4,073”.

Convert Text Date to a Date Format

In the example below, the following formula has been entered into cell C2. The date loses its format when combined in a text string. This is now useless.

=A2&" - "&B2
Date loses formatting when concatenated with text

By using the TEXT function, the date can be concatenated, and an appropriate date format applied.

The number format must be entered within the double quote marks. If these are omitted, the #NAME? error is displayed.

The following formula formats the date in a dd/mm/yyyy format.

=A2&" - "&TEXT(B2,"dd/mm/yyyy")
Format text as a date in Excel

You can specify the date format you want by using the same formatting characters that you get when applying custom formatting in Excel.

The examples below show some alternative representations of the date.

dd mmmm yyyy12 April 1974
dddd dd mmm yyyyFriday 12 Apr 1974
ddmmyyyy12041974

Convert Text Number to a Currency Format

The Excel TEXT function can be useful for displaying summary text that you see on reports and dashboards in Excel. For example, “Total spend for the current month is £2,930.89”.

The formula below calculates the net income value, converts it to text and applies a currency format.

="Net income for this month is "&TEXT(G2-H2,"£#,###.00")
Excel TEXT function to display text numbers in a currency format

When applying number formatting the # symbol displays a number only if necessary, and a 0 forces the display of a number.

The TEXT function is very versatile and there are many examples for its use.

Related Posts:

  • Advanced SUM Function Examples - The Power of SUM
    Advanced SUM Function Examples - The Power of SUM
  • Entering Fractions in Excel
    Entering Fractions in Excel
  • Moving Average in Excel
    Moving Average in Excel
  • SEQUENCE Function in Excel
    SEQUENCE Function in Excel

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • Moving Average in Excel
  • Excel IMAGE Function – Insert Images from a Cell Value
  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·