Report: Cell #1, Cell #2, Cell #3, Cell #4

[JemyM]

I am stuck again and would love som help

I would like to repeat all words found inside ~25 cells, separated only
by ", ", ignoring empty cells.

Data:
A1: [Apple ]
A2: [Orange]
A3: [Banana]
A4: [Tomato]
A5: [Syrup ]
A6: [ ]
A7: [ ]
A8: [ ]

The result should be something like:
[Apple, Orange, Banana, Tomato, Syrup]

 

[Bob Phillips]

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i,"A").value <> "" Then
Range("B1").Value = Range("B1").Value & Cells(i,"A").Value & ","
End If
Next i

With Range("B1")
.Value = Left(.Value, Len(.Value)-1)
End With

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Paul B]

JemyM, here is another option,

Function ConCatRange(CellBlock As Range) As String
'By: Gord Dibben, **changed for your needs**
'concatenate only the non-empty cells puts a , between them
'******Usage is: =ConCatRange(A1:A25)

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) - 1)
End Function

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[JemyM]

Ok, I am greatful for the 2 examples given here. I guess they are both
visual basic scripts and that's above my level. If someone have a
website that describes how I can use this in Excel I would be greatful.

 

[Bob Phillips]

See http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[random1970]

JemyM,

Looks like I'm not the only one out there who doesn't use VB. Here's a
simple solution to your problem.

In cell B1, type the formula =if(and(istext(A1),istext(A2)),",","")

This checks to see if the cell to the left and the one below it both
have text in them (in which case there should be a comma followed by a
space). Fill this down to the bottom of your list.

In cell C1, type the formula =A1&B1. This concatenates the two. Fill
down again.

At the bottom of column C (say C26), type =CONCATENATE(C1,C2,C3...C25).
In full, not abbreviated like this - I'm just being lazy. This should
give you the list you want.

Let me know how you go.

 

[Ron Rosenfeld]

I am stuck again and would love som help

I would like to repeat all words found inside ~25 cells, separated only
by ", ", ignoring empty cells.

Data:
A1: [Apple ]
A2: [Orange]
A3: [Banana]
A4: [Tomato]
A5: [Syrup ]
A6: [ ]
A7: [ ]
A8: [ ]

The result should be something like:
[Apple, Orange, Banana, Tomato, Syrup]

I note that you have opening and closing brackets around each word. Are they
really there or is that some kind of metacharacter?

In any event, one way is to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr/

You can then use the formula:

"["&REGEX.SUBSTITUTE(MCONCAT(REGEX.MID(
A1:A25,"\w+"),", "),"(?<=, )(, )+|(, )+$")&"]"

If the brackets are not really part of your cell entries, or the result, then
you can use the simpler formula:

=REGEX.SUBSTITUTE(MCONCAT(I1:J25,", "),"(?<=, )(, )+|(, )+$")



--ron

 

[JemyM]

I note that you have opening and closing brackets around each word. Are
they
really there or is that some kind of metacharacter?

In any event, one way is to download and install Longre's free
morefunc.xll
add-in from http://xcell05.free.fr/

You can then use the formula:

"["&REGEX.SUBSTITUTE(MCONCAT(REGEX.MID(
A1:A25,"\w+"),", "),"(?<=, )(, )+|(, )+$")&"]"

If the brackets are not really part of your cell entries, or the
result, then
you can use the simpler formula:

=REGEX.SUBSTITUTE(MCONCAT(I1:J25,", "),"(?<=, )(, )+|(, )+$")
--ron

The brackers were there to make it "look" like an excel stylesheet.
Sorry about the confusion.

Thanks for the tip. I will check it out

 

[JemyM]

JemyM,

Looks like I'm not the only one out there who doesn't use VB. Here's a
simple solution to your problem.

In cell B1, type the formula =if(and(istext(A1),istext(A2)),",","")

This checks to see if the cell to the left and the one below it both
have text in them (in which case there should be a comma followed by a
space). Fill this down to the bottom of your list.

In cell C1, type the formula =A1&B1. This concatenates the two. Fill
down again.

At the bottom of column C (say C26), type =CONCATENATE(C1,C2,C3...C25).
In full, not abbreviated like this - I'm just being lazy. This should
give you the list you want.

Let me know how you go.

Lol... that was a bit too easy...

I think I go with that one and spend more time with VB when I have the
time. Thanks!

 

[kletcho]

If you are trying to list all unique instances of a list of cells
containing words then I would use the advanced filter option under data
-- filters--advanced filters and check the unique records box. If you
have multiple words per cell divided by commas then you should try to
use text to columns to get one word per cell. No formulas required and
fast!

 

[kletcho]

If you are trying to list all unique instances of a list of cells
containing words then I would use the advanced filter option under data
-- filters--advanced filters and check the unique records box. If you
have multiple words per cell divided by commas then you should try to
use text to columns to get one word per cell. No formulas required and
fast!