Convert Number to Text. Cannot believe this is so hard. cell = Format ( cell,"###" )

[Xceller]

Thanks for your help in advance. Below is my code:

Sub ConverNumberToText()

For Each cell In Selection
cell = Format(cell, "###")
Next

End Sub

 

[farmerscott]

Hello,

have you tried =TEXT() function?

 

[Xceller]

Hello,

have you tried =TEXT() function?

Yeah, tried the Text() function, but doesn't work.

 

[farmerscott]

Xcellar,

did it still act like a number after using TEXT()? What did it do?

Am not sure if this will work- =cell(A1)&"".

Why do you want the number as text?

FarmerScott

 

[mikerickson]

The problem is that Excel's cell entry routine helpfuly notes that you have entered a numeral and converts it to a number.
Try

cell.Value = "'" & Format(Val(CStr(cell.Value)), "###")

(The CStr is there solely for error handling if some cell contains an error value (like #DIV/0). It does nothing to make the cell contents a string.)

 

[Xceller]

Hi http://www.mrexcel.com/forum/members/mikerickson.htmlmikerickson, Your code works. I also tried cell.Value = "'" & cell and it works too. Thank you.
farmerscott, thank you for looking into this. I wanted to convert number to text because I wanted to compare two lists, which contain numeric and text. For example, if I compare 002 to 2 as value they would be the same, but in reality they are not that's why I have to convert them to text.