Prev: Sorting a text strings but omitting preceding "A" or "The"
Next: Show formula result in another sheet
From: peterh on 15 Dec 2009 16:35
I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.

Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL

From: Eva on 15 Dec 2009 17:06
Can you elaborate more about it? You mean cells or rows?
--
Greatly appreciated
Eva


"peterh" wrote:

> I am having trouble with a formula to concatenate the descriptions into 1
> cell for each S/C #, some have 2, 3 of 4 cells.
>
> Material No. Full Description
> 904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
> 9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
> BALL VALVES, ON ALUMINA KILNS
> 907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
> BLACK, PVC / V75, .6 / 1KV, 100M ROLL
> 907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
> PVC / V75, .6 / 1KV, 100M ROLL
>
From: ryguy7272 on 15 Dec 2009 17:59
Maybe this is what you want?

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k <> 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function

With your data in cells A1:E1, =mergem(A1:E1)

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Eva" wrote:

> Can you elaborate more about it? You mean cells or rows?
> --
> Greatly appreciated
> Eva
>
>
> "peterh" wrote:
>
> > I am having trouble with a formula to concatenate the descriptions into 1
> > cell for each S/C #, some have 2, 3 of 4 cells.
> >
> > Material No. Full Description
> > 904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
> > 9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
> > BALL VALVES, ON ALUMINA KILNS
> > 907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
> > BLACK, PVC / V75, .6 / 1KV, 100M ROLL
> > 907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
> > PVC / V75, .6 / 1KV, 100M ROLL
> >
From: Gord Dibben on 15 Dec 2009 19:05
Ryan

Just a head's up.

In cases where there are any blank cells in the range, your code will add
extra commas.

This revision will ignore blank cells.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function


Gord Dibben MS Excel MVP

On Tue, 15 Dec 2009 14:59:02 -0800, ryguy7272
<ryguy7272(a)discussions.microsoft.com> wrote:

>Maybe this is what you want?
>
>Function mergem(r As Range) As String
>mergem = r.Cells(1, 1).Value
>k = 1
>For Each rr In r
> If k <> 1 Then
> mergem = mergem & "," & rr.Value
> End If
> k = 2
>Next
>End Function
>
>With your data in cells A1:E1, =mergem(A1:E1)

From: peterh on 15 Dec 2009 22:09
Eva,

S/C #'s in Column "C"
Descriptions in Column "E"
Need result in Column "L"

"Eva" wrote:

> Can you elaborate more about it? You mean cells or rows?
> --
> Greatly appreciated
> Eva
>
>
> "peterh" wrote:
>
> > I am having trouble with a formula to concatenate the descriptions into 1
> > cell for each S/C #, some have 2, 3 of 4 cells.
> >
> > Material No. Full Description
> > 904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
> > 9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
> > BALL VALVES, ON ALUMINA KILNS
> > 907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
> > BLACK, PVC / V75, .6 / 1KV, 100M ROLL
> > 907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
> > PVC / V75, .6 / 1KV, 100M ROLL
> >
 |  Next  |  Last
Pages: 1 2
Prev: Sorting a text strings but omitting preceding "A" or "The"
Next: Show formula result in another sheet