Hi

I have a spreadsheet that has a quite a few cells that are formatted as text but need to be converted to numbers. Is there a quick way of doing them all at once? The are all in different rows and columns. Have tried launching Format Cells and changing there but with no luck.

Thanks.

Hello Viv,

If the cells are "all over" you need a way to address them. You can do a search for cells formatted as text but maybe not all should be numbers?

The code below will set all cells A:H in the active range as numbers with 2 decimals:

Dim r as Range
Dim rowCount as Integer

rowCount = ActiveSheet.UsedRange.Rows.Count
Set r = Range("A1:H" & rowCount)
r.NumberFormat = "#.00"

Hi Viv,

you could also multiply all your fields by 1. This will transform all texts containing only numbers into number format and will leave text fields unaffected. However, blank fields will be changed to 0.

Copy the value 1- select your table - rightclick mouse - paste special - multiply

BR

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

Thanks

George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please click "tnfsl@microsoft.com"