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

Xceller

Active Member
Joined
Aug 24, 2009
Messages
265
Thanks for your help in advance. Below is my code:

Sub ConverNumberToText()

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

End Sub
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906
The problem is that Excel's cell entry routine helpfuly notes that you have entered a numeral and converts it to a number.
Try
Code:
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.)
 
Last edited:

Xceller

Active Member
Joined
Aug 24, 2009
Messages
265
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,576
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top