Converting Numbers to Text to Keep Leading 0's - Dynamic Range

[ac1212]

Hi,

I am trying to covert numbers in in a column to text in order to keep the leading zero's. I have managed to get the below to work but want to be able to have the range as dynamic not set on the end being row 100.

I need to have all cells with numbers in column A to be 5 characters long with leading zero's if needed.

Thanks for the help!

  Dim cell As Range
   
    Range("A2:A100").NumberFormat = "@"
   
    For Each cell In Range("A2:A100")
        cell = Format(cell, "00000")
    Next cell

 

[Sanjeev1976]

ac1212 welcome to the forum.

You don't need a VBA solution for your case. You can simply select the column or range and press Ctrl + 1 and use 00000 custom format

 

[ac1212]

ac1212 welcome to the forum.

You don't need a VBA solution for your case. You can simply select the column or range and press Ctrl + 1 and use 00000 custom format

Thanks for the response Sanjeev. I should have clarified that this is to format numerous columns from an accounting database. There are other formatting issues that I have already resolved, just stuck on this one.

 

[steve the fish]

You need to be a little bit more clear as to what you want here. Formatting doesnt change the value of a cell. It changes its appearance. So when you say you want the cells to be all 5 characters long then formatting isnt going to make them longer but they will just appear longer.

For example lets say your cell contains the number 1. Changing the format can make that appear as 00001 but the underlying value is still 1 and =LEN(A1) would still produce 1 not 5.

So do you want the cell value to actually be 1 or 00001? There is a difference.

 

[ac1212]

You need to be a little bit more clear as to what you want here. Formatting doesnt change the value of a cell. It changes its appearance. So when you say you want the cells to be all 5 characters long then formatting isnt going to make them longer but they will just appear longer.

For example lets say your cell contains the number 1. Changing the format can make that appear as 00001 but the underlying value is still 1 and =LEN(A1) would still produce 1 not 5.

So do you want the cell value to actually be 1 or 00001? There is a difference.

I want the cell value to be 00001.

Essentially the code I originally posted does the job, however its only for the range A2:A100. I need this to be on dynamic, the range in column A could be 2 rows or thousands depending on the data so want this to be done for all cells with data in column A.

 

[steve the fish]

For exactly the reasons i prescribed your original code doesnt do the job. You are just formatting. You are NOT altering the cell value at all. You are changing the appearance.

 

[steve the fish]

If you just want formatting then why not just format the entire column:

Columns("A:A").NumberFormat = "00000"

You never need to loop to do that.

 

[ac1212]

For example currently there is “1201” in a cell in column A, I need this to be changed to “01201”. The original code I posted does this. However the data is not always the same number of rows, if I change the range from “A2:A100” to “A:A” it causes excel not to respond. I assume because it is trying to change all rows in column A, that’s why I want to change it to a dynamic range that only changes the cells in column A that contain data.

 

[Fluff]

How about

   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .NumberFormat = "@"
      .Value = Evaluate(Replace("if(@="""","""",text(@,""00000""))", "@", .Address))
   End With

 

[ac1212]

Thanks Fluff, this does what I need!