Hiding the leading apostrophe

[farmerlaw]

I have zip codes which should have leading "0's" but excel automatically eliminates leading zeros. I realize that I could format the number to display 5 digits thus forcing a leading zero to appear. But that is just formatting and does not actually add a zero to the number. I want the contents of the cell to actually contain the leading zero.

As a work-around, I use "Concatenate" to add an apostrophe followed by "0" to the beginning of the zip codes. I then "paste special: values". The issue is that the leading apostrophe is now visible in the body of the spreadsheet - it does not automatically hide like it would if I manually typed '0 into each cell.

I can hide it on a cell-by-cell basis by clicking into each cell - but I have thousands of cells. So my question is whether anyone knows how to hide a (visible) leading apostrophe on a large batch of cells? Said another way, is there something I can do that would have the same effect as manually clicking into each individual cell?

'07830
'07830
'07830
'07830
'07853
'07901

 

[Joe4]

Welcome to the Board!

Where does the data come from?
How does it get into Excel?
Are you working with data that is already populated, or are you populating the data manually?

How we approach it makes a difference if we are dealing with an already populated Excel sheet, or whether we are bringing the data into Excel or manually entering it.

 

[farmerlaw]

The source of the data varies but typically it is either already in a sheet that I am given or I cut-and-paste it into a sheet. But the genesis of the problem is when I concatenate to add the apostrophe-zero. From there I have what I asked for, but the apostrophe lingers in the body of the spreadsheet until I click into each individual cell. So, the issue occurs when I concatenate no matter where the data comes from.

 

[Joe4]

Yes, the fix I am thinking about will not require you to add an apostrophe and 0.
What does the data look like originally, so we can create something for you that will run on your data, exactly as you receive it, without having to do any manual steps (like adding an apostrophe and zero to it)?
Has the Zip Code format already been applied, or does it just come over as numbers of lengths of 5 or less?
Are they always 5 digit zip codes, or do you also have some extended zip codes (9 digits)?

 

[Joe4]

If they are should be 5 digit zip codes and are coming over as numbers, this code should convert them to string values with leading zeroes without apostrophes.
This is set to work for column A, but you can change the column reference in the code to any column you like:

Sub MyConvertMacro()

    Dim c As String
    Dim lr As Long
    Dim r As Long
    
'   Designate column to apply to
    c = "A"

    Application.ScreenUpdating = False

'   Find last cell in column A with data
    lr = Cells(Rows.Count, c).End(xlUp)
    
'   Format column as a string
    Columns(c).NumberFormat = "@"
    
'   Loop through rows and turn each value into 5 digit zip
    For r = 1 To lr
'       Check to see if entry is a number
        If Cells(r, c) <> "" And IsNumeric(Cells(r, c)) Then
'           Make into a string
            Cells(r, c).Value = Format(Cells(r, c), "00000")
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[farmerlaw]

It really varies. But the most common starting point is receiving a spreadsheet with the data pre-populated. I have another work around that involves formatting the cells as "text" but that involves a few extra steps and isn't as efficient as concatenating. So, I am really looking for a way to hide the leading apostrophes that appear in large groups of cells after concatenating with a '0

 

[farmerlaw]

I am not using 9 digit zip codes - only 5 digits.

 

[farmerlaw]

If they are should be 5 digit zip codes and are coming over as numbers, this code should convert them to string values with leading zeroes without apostrophes.
This is set to work for column A, but you can change the column reference in the code to any column you like:

Sub MyConvertMacro()

    Dim c As String
    Dim lr As Long
    Dim r As Long
   
'   Designate column to apply to
    c = "A"

    Application.ScreenUpdating = False

'   Find last cell in column A with data
    lr = Cells(Rows.Count, c).End(xlUp)
   
'   Format column as a string
    Columns(c).NumberFormat = "@"
   
'   Loop through rows and turn each value into 5 digit zip
    For r = 1 To lr
'       Check to see if entry is a number
        If Cells(r, c) <> "" And IsNumeric(Cells(r, c)) Then
'           Make into a string
            Cells(r, c).Value = Format(Cells(r, c), "00000")
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

I will try this. Thank you! I don't have any VBA experience but this would be a good time to learn. I will let you know how it goes.

 

[Joe4]

I don't have any VBA experience but this would be a good time to learn. I will let you know how it goes.

Here is a good tutorial to show you where to put my code and how to run it: Insert and run VBA macros in Excel - step-by-step guide.

 

[shinigamilight]

Change the formatting of a bunch of cells to text and then it will allow you to add zeroes in front of the values.