Formatting Made Easy - TEXT Function 😇

The TEXT function is the most underrated one in Excel. Believe me or not, if used correctly this function will save tons of your time ⏳ while processing or concatenating multiple cells with different formats.

The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It’s useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.

💰 & 🗓️ Combining Amount and Date in the Single Cell

Let us take an example, you have the USD value of an invoice in one cell, the USD to INR Exchange rate in the adjacent cell, and the Shipping Bill date in another cell. Image for quick glance:

Case Study -1 (Problem Statement)

Requirement: Combine the INR invoice value with the shipping bill date separated by “;”.

First, let us try the formula with simple & operator (CONCATENATE or CONCAT Functions can also be used):

Case Study -1 (First Try)

As you see in the L column, the formatting was not pretty good as the formula fetches the raw data as results. To correct this formatting error our savior comes to rescue TEXT Function 🤩.

1. Amount Formatting: “#,#0.00”
2. Date Formatting: “DD-MM-YYYY”

Refer to this link for more number, time and date formats.

Case Study -1 (Solution)

📃 Write a Narration

Requirement: Write a detailed Narration using the raw data available in the table along with the TEXT Function.

Narration (Sample):

Being Sales — Inv No. EXP/001/22–23 Rs. 11,28,112.40 (Free on Board) through Chennai Air Cargo (INMAA4) via Shipping Bill No. 9757879 dated 18–04–2022.

Raw Data:

Raw Data for narration

✔️ Extract of Formula:

Narration Formula

️🧐Now, let us decode the above formula line by line:

1. To insert the Text within the narration, use double quotes (“ ”).
2. To change single-digit Invoice no. into 3 digits, use the TEXT function with this format “000”.
3. To convert the USD value * Ex. Rate into INR Value (thousand separators with 2 decimals), use the TEXT function with this format “#,#0.00”.
4. To bring data in exact date format, use the TEXT function with this format “DD-MM-YYYY”.
5. To bring extra data into the narration like the full form of shipment terms and the Port Name of the respective customs port code, use the VLOOKUP function with the name range feature in Excel.

💡 Bonus Tip: Elaboration on Point No. 5

While using the VLOOKUP function in these kinds of formulas don’t link the cells of the table array directly and lock the cells using $ (F4) instead, name the table range by selecting the range and Alt + F3 or Name Box. Reason: as the formula already gets complicated by the usage of different functions, we restrict the cell references outside the main raw data table.

Shipment Terms

Port Code

💡 Bonus Tip: Other Formatting Techniques

1. Serial Number Formatting
2. Sub-Division Serial Number Formatting
3. Extract the Day, Month, and Year from the Date
4. Structured Mobile Number or Telephone Number

📋 List of Formulas with Raw & Formatted Data:

Example: Other Formatting Techniques

λ - TEXT Function with LAMBDA Function:

If you have access to Microsoft 365 or the Web Version of Excel and are willing to learn the LAMBDA function (λ):

Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions.

However, before formulating the LAMBDA function we have to narrow down the existing formula from having multiple cell references to single cell references using the OFFSET function.

The OFFSET function Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and columns to be returned.

Redrafted formula using the OFFSET function:

using the OFFSET function

Formulating the LAMBDA function:

using the LAMBDA Function

Assign Name (“Narration”) to the above LAMBDA Function to create Custom Function:

Formulas >> Define Name (you can define any names except the existing names and default functions and add comments for future reference).

Name Defining and assigning custom function

📋 Final Output:

Custom Narration Function

If you reached up to this part of the post, I really appreciate your interest and patience to learn Advanced Excel tools. For a more such detailed article on Excel like this one, follow me on Medium Aswin Sivakumar.

Hi Folks, I am Aswin 😎, a qualified Chartered Accountant having 5 years of experience in the field of Finance, Accounts, and Auditing, currently working as an Executive — Finance & Accounts @ Jay Jay Mills (India) Private Limited (Multinational Garments Manufacturing Company).

Check out my Portfolio: Aswin’s Portfolio

LinkedIn Profile: Aswin Sivakumar

Sample Excel File available at Telegram Group

Image for Post Highlight