Article 1 – Convert Number Format into Text Format

The very first post which I want to write about is conversion of numbers into text. For example, if have a numeric value of 2.23, it should be converted to text value of 2.23. We may need this in many data manipulation scenarios. I will not go into why we need this but directly jump into writing about this topic.

In my view, there are always 4 ways to achieve anything in Excel

1. Formula
2. Manual
3. VBA
4. Hybrid (Combining minimum 2 of above methods)

Let's touch on all 4 ways. For the purpose of this article, let's assume a numeric value of 2.23 is contained in cell A1.

But first, after conversion, how will you know that you number is converted into text. You can use any one method from below –

1. Numbers are aligned right and Texts are aligned left by default unless you change the alignment. Hence, after conversion, you will notice that right aligned number is changed to left aligned number (which is text now, in fact). {Note – This is not a foolproof method}

2. Use the formula ISTEXT. If converted Number is contained in B1, use formula = ISTEXT(B1). If result is TRUE, it is Text. This is the best and foolproof method as it doesn't depend upon alignment.

3. Use the formula T.  If converted Number is contained in B1, use formula = T(B1). If B1 is Text, result will be B1 itself. If not Text, result will be blanks. Hence, if 2.23 is Text, result will be 2.23. But if 2.23 is not text, result of T will be blanks.

4. Most of the time, after conversion, you will notice a very small green triangle in top left corner indicating that this is a text.

If you select the cell, you will get a warning sign next to it which tells you that this is a number stored as Text.

 

 

 

 

 

1. FORMULA WAY

1.1: = A1&""

1.2: = ""&A1

Best & fastest way to convert into text is concatenate null character "" into numeric as in 1.1 and 1.2

1.3: = TEXT(A1,"@")

1.4: = TEXT(A1,"General")

Word "General" is case-insensitive. Hence, you may write "General" as "general", "GeNERal" or any other way. It will not make any difference. Also, if your number doesn't contain decimal, you may also use

1.4.1 = TEXT(A1,"0")

Utilizing Text function for other custom formatting is a very very big topic in itself. I will write an article on this also.

Now, you may use other character functions of Excel to force numeric into text.

1.5: = LEFT(A1,LEN(A1))

1.6: = RIGHT(A1,LEN(A1))

For LEFT and RIGHT, it there are no decimals, then LEN function is not needed. Hence, formulas simply become LEFT(A1), RIGHT(A1)

1.7: = LOWER(A1)

1.8: = UPPER(A1)

1.9: = PROPER(A1)

1.10: = REPT(A1,1)

1.11: = TRIM(A1)

1.12: = MID(A1,1,LEN(A1))

1.13: = SUBSTITUTE(A1,A1,A1)

1.14: = REPLACE(A1,1,LEN(A1),A1)

2. MANUAL WAY

2.1 In the Ribbon, under Data>Data Tools, you will notice Text to Columns button. Click it.

The wizard which appears, click Next 2 times to reach on steps 3. Here, select Text to convert into Text.

This is a preferred method when you data in many rows and it provides conversion in straight forward manner without writing any formula.

 2.2 You just precede number with one apostrophe. Hence, if you modify 2.23 as '2.23, it will be entered as Text. You will not see this apostrophe in the cell but you can see this apostrophe in the formula bar.

CAUTION – On many sites, you will notice the tip that right click>Format Cells>Text in Numbers tab produces Text. There is a catch in this. If cell has a number and you format by this method, the number will become left aligned but it will not be converted into text. You can check this by ISTEXT or T function.

But if cell is blank and you do right click>Format Cells>Text in Numbers tab and then input number, it will be Text.

So, beware of this fact.

3. VBA WAY

Though the conversion from Number to Text is so straight forward, that VBA codes are not required. But, you know, for repetitive tasks, it is better to have macros. And macros use VBA codes.

The way this will be used depends upon your uses and needs. Hence, there may be N ways to write VBA code to achieve your tasks.

Cstr is the recommended function to convert Numbers into Text.

If your number value is in a variable called N#Number and Text value should be in a variable called T#Text, so you may put following in your code

T#Text = Cstr(N#Number)

If N#Number is 2.23, T#Text would become 2.23 in text format.

4. Hybrid WAY

I don't think that there is a need of any Hybrid way here. All 3 above methods are sufficient to achieve the purpose.