An upgrade to the Text() function in Excel

Struggling to Excel

Many of us are familiar with the Text() function. It comes in handy when you set up excel to draft reports for you. It converts a numeric value stored in a cell to a string based on  the format specified by the user. The only thing I hate about that function is that you have to specify the format every time you use it. Another drawback is, the format of the text is not linked to the cell formatting of the cell that is being referred to. For instance, if you later decide to change the cell formatting of a cell, you also have to change the Special-Format-String argument in the Text() function.

Wouldn’t it be great if there were a function that formats the string based in the cell’s format? Check out the code below.

This function first tries to use the ‘NumberFormatLocal’ property, and moves on to use the ‘’NumberFormat’ property if it encounters an error. The function is set to be Volatile by default. However, the user has the option to make it a non-volatile function if the workbook is considerably large. Click here to read more on Volatile functions.

'Function to convert a numeric value to a string based in the
'cell formatting
'Author     : Ejaz Ahmed
'Date       : 14 November 2013
'Website    :
Function AutoText(ByRef WhichCell As Range, _
                    Optional ByVal ForceVolatile = True) As Variant

'Set to Volatile by default, the user could also choose for the
'function to be non-volatile
Application.Volatile (ForceVolatile)

'Return a reference error if the range refers to more than one cell
If WhichCell.Count > 1 Then
    AutoText = CVErr(xlErrRef)
    Exit Function
End If

On Error Resume Next
'use the NumberFormatLocal property if exists
AutoText = Application.WorksheetFunction.Text(WhichCell.Value2, WhichCell.NumberFormatLocal)
'if excel encounters an error, use the NumberFomat property
If Not Err.Number = 0 Then
    AutoText = Application.WorksheetFunction.Text(WhichCell.Value2, WhichCell.NumberFormat)
End If
On Error GoTo 0

End Function

Published by Ejaz

I specialized in actuarial science in my masters’ program and I am determined to become a qualified actuary. I invest extra time and effort into creating intuitive spreadsheet applications that improve productivity at the workplace. I also enjoy spending my leisure time creating digital art; I publish my designs on my art in this blog named Designecdotes. Most of the people who have dramatically influenced my life were enthusiastic academicians. I believe that the greatest gift in life is having a nurturing mentor, and I would like to be that for someone. I owe it to my future mentees to be the best at what I do, and I consciously direct my endeavors at gathering what I consider essential traits of a seasoned mentor.

One thought on “An upgrade to the Text() function in Excel

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: