Unexpected issue with VBA code received

[srosetti]

What the code does is moves the number data in excel from column C and
appends it to B.
It also formats that data to be in a "00000" data format because the
source data on my example from column C row 1 would look like 11.

The end result is it might look like this.

Widget 00011

My unexpected issue is that now I have some data that I believe excel
doesn't treat as a number. Some of the data

Example source

Column C row 2 = 11602-72401 Column B row 2 =Small Widget

end result = Small Widget11602-72401

as you can see the space between the txt and number don't exist. I
think because Excel treats the 11602-72401 number as text.


Here is the sample code:

Sub ConcatBandC()
Dim X As Long, LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For X = 2 To LastRow
Cells(X, "B").Value = Cells(X, "B").Value & Format( _
Cells(X, "C").Value, " 00000")
Next
End Sub

Any ideas on fixing the code so it will handle both examples?

Thanks

 

[Rick Rothstein]

Well, 11602-72401 might be a number to you; but, since pure numbers do not
have symbols in the middle of them, then both excel and VB will see it as
text. However, if you move the space character outside of the Format
function's format pattern, I think your code will do what you want. Try it
this way and see if it works...

Cells(X, "B").Value = Cells(X, "B").Value & " " & Format( _
Cells(X, "C").Value, "00000")

 

[srosetti]

Well, 11602-72401 might be a number to you; but, since pure numbers do not
have symbols in the middle of them, then both excel and VB will see it as
text. However, if you move the space character outside of the Format
function's format pattern, I think your code will do what you want. Try it
this way and see if it works...

    Cells(X, "B").Value = Cells(X, "B").Value & " " & Format( _
                          Cells(X, "C").Value, "00000")

It works great. Thank You