EXCEL: Concatenating a Range of Cells

In VBA, you could do it this way:
In a module, paste a custom function:

Function ConcatRange(range As Range, containing As String, separator As String) As String Dim result As String, c As Range For Each c In range If InStr(c.Value, containing) > 0 Then result = result & c.Value & separator End If Next c ConcatRange = Left(result, Len(result) - Len(separator)) End Function

In a sheet, 'insert / function / custom' and choose ConcatRange.
This function expects 3 parameters:
- range: the range to scan (select it with the mouse)
- containing: the string that must be contained in each cell of the range to be included in the concatenation.
- separator: the string separating each concatenated cell (1 or more characters)

Done roughly with the elements you provided, may require adaptations for your specific case.
example:
http://www.cijoint.fr/cjlink.php?file=cj200808/cijryw0Xms.xls

eric

Hello!

Thank you for your response.
Actually, I want to search for a list of characters in a range of cells and keep the results.

Example:
I want to search in the range A1:A100 for all cells containing text that starts with "R" and keep all the results. The second part is to display the results separated by spaces in a single resulting cell.

As for the "concatenate" function, it doesn't work if you insert a row between cell A1 and cell A3... It will not take into account the 4 rows for concatenation. However, I need to insert rows to add names.
The function I want to implement allows me to display a summary solution.

Thank you in advance for your response,
Best regards,
Bertrand

Hello,

I didn't quite understand your research story, but for concatenating a range of cells with spaces between the strings, you can try: =CONCATENATE(" '";A1;" ";A2;" ";A3;" ") (for range of cells A1:A3)

I hope that helps you,
Good luck

Hello,

And how can I do it in VBA?

Thank you in advance for your reply,
B.

THANK YOU both! I will try to put all of that into practice..!
I’ll keep you updated!
++
B.

However, I have an ADSL problem at home, and I may have difficulty connecting until Thursday evening...
Eric

I did as follows
Col A | Col B
Txt1 | =A
Txt2 | =CONCATENATE(A2;B1) Result "Txt1Txt2"
Txt3 | =CONCATENATE(A3;B2) Result "Txt1Txt2Txt3"
etc...

RB

Hello,

I just posted my file as requested. The link is: https://www.cjoint.com/c/CIgjYwaN3hI

Best regards

Hello,

possible only in VBA...
eric

Hello everyone

What I'm going to ask may seem quite complicated, but it would be great to find an answer since I've been searching for days in vain...

I have a column A with some cells containing characters, others with numbers, and some empty. On the other side, I have a column B with cells containing characters and others empty. In fact, the cells in column B contained sentences that got split, and some words were sent to the cells in column A. I would like to create a macro that searches in column A for the cells containing the returned characters (the fragments of split sentences) and copies them into column B in the targeted cells (so that the sentences can be reformed). Since the cells containing numbers and the empty cells don't interest me, I would like the macro to ignore them and continue its search (in column A) until it finds a cell containing characters.

I'm not sure if I've expressed myself well, but if someone is skilled enough in this area, it would be nice to offer me an answer and it would really help me a lot ^^