2 issues really

[srosetti]

Ok, yesterday I asked how to append some information on to the end of
a file. Today I have a question with some twists to it.

1)
In Column 'C' I have a product number field. It consists of 5
digits. An example of a product number would be. 00010 The zero's in
front of the 10 are important.
I'll give you other examples so you can fully understand how many
numbers I might have.
00010
00100
01000
10000
Those are basic examples of what they might look like..could be any
counting numbers, but will always have the 0 place holder to bring the
digits to a total of 5.

2)
In Column 'B' I have free form product name. I.e. Widget or Very Very
small widget
I need to append the values in Column 'C' to the end of Text in Column
'B'

My data should look something like this..

Field B2 reads Widget
Field B3 reads Very Very small widget
Field C2 reads 00100
Field C3 reads 00010

I want my final data output in Field B2 and B3 to read
B2 Widget 00100
B3 Very Very small widget 00010

If you have any questions..please feel free to ask.

Thanks

 

[broro183]

hi,

This is posted in the Programming forum but here are a couple o
non-vba solutions...

1)
- select the cells, press [ctrl + 1], Number - Custom, & type 0000
into the Type field.
- Or, using a helper column eg column D, type
Code
-------------------
=TEXT(C1,"00000"
-------------------
& copy down as needed.

2)
- In a helper column, eg column D, type
Code
-------------------
=B2 & " " & C
-------------------
, copy down as needed, select all the cells, press [ctrl + c], selec
cell B2 & press [alt + E + S + V] to paste special as values over th
top of the original data.

If you do still want a macro solution, record a macro as you complet
the actions manually & then post the recorded code if you need hel
making it flexible.

hth
Ro

 

[Rick Rothstein]

Give this code a try..

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

 

[srosetti]

Give this code a try..

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

--
Rick (MVP - Excel)

hi,

This is posted in the Programming forum but here are a couple of
non-vba solutions...

1)
- select the cells, press [ctrl + 1], Number - Custom, & type 00000
into the Type field.
- Or, using a helper column eg column D, type
Code:

2)
- In a helper column, eg column D, type
Code:

If you do still want a macro solution, record a macro as you complete
the actions manually & then post the recorded code if you need help
making it flexible.

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile:
http://www.thecodecage.com/forumz/member.php?userid=333
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=144631

The code populates my description field perfectly. I'll be sure and
use it.
Thank You so much.

 

[srosetti]

hi,

This is posted in the Programming forum but here are a couple of
non-vba solutions...

1)
- select the cells, press [ctrl + 1], Number - Custom, & type 00000
into the Type field.
- Or, using a helper column eg column D, type
Code:
--------------------
    =TEXT(C1,"00000")
--------------------
 & copy down as needed.

2)
- In a helper column, eg column D, type
Code:
--------------------
    =B2 & " " & C2
--------------------
, copy down as needed, select all the cells, press [ctrl + c], select
cell B2 & press [alt + E + S + V] to paste special as values over the
top of the original data.

If you do still want a macro solution, record a macro as you complete
the actions manually & then post the recorded code if you need help
making it flexible.

hth
Rob

--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...

The Text formatting works great. I think the VBA code below works
easiest for migrating the B and C column. Even though the VBA code
works..I really like the flexibilty of using the helper column also to
join the columns. Each have their application. Thank you so much.