Store Number as Text

[jfish1288]

I'm using an excel file to generate a list of UPC codes to send to a printer.
The UPC code needs to be 12 digits.
In the source excel file the UPC codes are stored as text with a leading zero.
Using the code below, they are populating the new list as numbers without the leading zero.
Formatting as a 12 digit number does not work because the printer does not read the leading zero.
It needs to be stored as text.
What am I doing wrong?

Source sheet examples (number is stored as text with the little green corner in the cell and the warning sign)
[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[TD]ColumnE[/TD]
[TD]UPC[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D3[/TD]
[TD]E4[/TD]
[TD]097963547574[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[TD]D3[/TD]
[TD]E3[/TD]
[TD]097963664127[/TD]
[/TR]
</tbody>[/TABLE]

Current Output
[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]UPC[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]97963547574[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]97963664127[/TD]
[/TR]
</tbody>[/TABLE]

Dim UPC_code As String

UPC_code = sourceWS.Cells(iRow, 6).Text
expandWS.Cells(printRow, 3).Value = UPC_code

 

[Joe4]

The ".Value" is coercing your text value back to numeric. Get rid of it here:

expandWS.Cells(printRow, 3)[COLOR=#ff0000].Value[/COLOR] = UPC_code

like this:

expandWS.Cells(printRow, 3) = UPC_code

 

[jfish1288]

I still have the same issue

 

[Modesto73]

If the cell is formatted as number or general, it will interpret a number as a number, whether that number is stored in VBA variables as a string is irrelevant. I would suggest trying to format the cell/range as text fist:

Dim UPC_code As String


UPC_code = sourceWS.Cells(iRow, 6).Text
expandWS.Cells(printRow, 3).NumberFormat = "@"
expandWS.Cells(printRow, 3).Value = UPC_code

 

[jfish1288]

If the cell is formatted as number or general, it will interpret a number as a number, whether that number is stored in VBA variables as a string is irrelevant. I would suggest trying to format the cell/range as text fist:

Dim UPC_code As String


UPC_code = sourceWS.Cells(iRow, 6).Text
expandWS.Cells(printRow, 3).NumberFormat = "@"
expandWS.Cells(printRow, 3).Value = UPC_code

That did it. Thanks!

 

[Joe4]

I still have the same issue

Odd. It worked for me when I tested it out.
My next suggestion was going to be to pre-format the cell/column/row as Text, but looks like Modesto73 already got you there.