VBA To Convert Number to Text, Keeping Leading Zeros

[AndyPandy27]

Hi All,

I'm very new to VBA, but have been experimenting to get a bit better, however I'm stuck and would greatly appreciate some help.

I have ~85,000 rows of data in a "Master Price List", with Product Codes listed in B5:B85000.

However, some of the Product Codes, all of varying length, are currently in a custom number format, to ensure leading zeros are displayed, where applicable. The customer number format is:

[>9999]000000;General

In order to convert these to text as "quickly" as possible (please let me know if I am doing this all wrong!), I have written the following VBA:

Sub SingleQuote()           
 For Each Cell In Selection
  If Not Cell.HasFormula Then
   Cell.Value = Chr(39) & Cell.Value
  End If
 Next Cell
End Sub

This works to convert the numbers to text, but it removes the leading zero for those codes which currently have it added based on my custom number format.

Is there any way of being able to amend the above code, to ensure that the numbers which need to have a leading zero, continue to do so?

The criteria for which cells need a leading zero are as follows:

1. The cell in Column G of the same row as the Product Code in Column B = 50
2. The length of the Product Code in Column B = 6

Please let me know if you need any further information from me.

Many thanks,

AP

 

[pgc01]

Hi

If I understand correctly you are happy with the text displayed in the cells.
If that's the case you can use it as the value for the cells.

Try:

Sub SingleQuote()
Dim r As Range, s As String
 
For Each r In Selection
    If Not r.HasFormula Then
        s = r.Text                     ' gets text displayed in cell
        r.NumberFormat = "@"     ' sets cell format as text
        r.Value = s                    ' writes the new value in the cell
    End If
Next r
End Sub

 

[diddi]

see if this works

Sub SingleQuote()           
   For Each Cell In Selection
       If Not Cell.HasFormula Then
            Cell.Value = cstr(format(Cell.Value,"000000"))
       End If
   Next Cell
End Sub

 

[AndyPandy27]

Hi PGC,

That has worked like a charm - thank you very much indeed, much appreciated.

AP

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[OilEconomist]

see if this works

Sub SingleQuote()          
   For Each Cell In Selection
       If Not Cell.HasFormula Then
            Cell.Value = cstr(format(Cell.Value,"000000"))
       End If
   Next Cell
End Sub

@diddi This is awesome! Was able to use it when I was joining to cells (a date) and some text ("->") and row no. and get the row number in the format ("0000"). Thanks!