Excell to Notebook

[chris ashdown]

Hi Folks

I have a list in excel with company name in one column and a product ref in the next column

I am trying to add to addwords and need the two joined together with a single space between the two parts and not a tab as at present

Company Product
Alexandra W112
Alexandra W113

to

Alexandra W112
Alexandra W113

Without either a comma between or a tab space

Quite a lot to do so looking for a automated way if possible

[chris ashdown]

Sorry dont seem to be able to show top part with a tab space between Alexandra and the product if you get my meaning

[Mike Hughes]

This should do it.

1. Create a macro
2. Copy the following code into it.
3. Save and run

Sub Combine()

' combines two columns into the first

' set the first row to look at

n = 2
somethinghere = ""

somethinghere = Cells(n, 1).Value

Do While somethinghere <> ""

Firstbit = Cells(n, 1).Value
secondbit = Cells(n, 2).Value

Combined = Firstbit & " " & secondbit

Cells(n, 1).Value = Combined

n = n + 1

somethinghere = Cells(n, 1).Value

' trap for failure to find empty cell

If n > 5000 Then Exit Do

Loop

MsgBox "Total rows combined = " & n

End Sub

This will combine the first and second columns into the first with a space between them. It will overwrite the first column so best to back up your file first.

The loop will stop at the first blank entry it finds.

Mike

[chris ashdown]

Many thanks Mike only 740 to do so I was not looking forward to manual edit

[fergusw]

Another (simpler?) way in Excel is:

Column A Column B
Product 1 123
Product 2 234

1.CLick into C1 (the next cell on the first row)
2. In the Formula bar type

A1 & " " & B1

3. Click "tick" or hit return
4. Click-drag the bottom right corner of the Cell C1
5. Drag it down the whole C column where you want the same formula applied.