EXCEL: Concatenating a Range of Cells Solved

Question

Hello,

I want to perform a process on a range of Excel cells without using macros...
What I want:
1- To search in a range of Excel cells to find the cells containing the information I'm interested in and retrieve these cells (or rather the information from the cells).
2- To concatenate all the results from the previous search separated by a space in a result cell.

I can't even concatenate a range of cells, the function doesn't seem to work on a range and I can't find anything in the forums on this topic...
HELP!

Thank you in advance,
B.

klrblz · Accepted Answer

you can maybe tweak it like this:

to concatenate cells A1 to A10:
In B1 you put =A1
In B2 =B1&A2
And then you drag your formula down the entire column B it should concatenate your entire column A

I hope it's clear, there must be a better solution but for now I'm stumped

klrblz · Answer

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

BertyBoy · Answer

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

eriiic · Answer

Hello,  possible only in VBA... eric

BertyBoy · Answer

Hello,  And how can I do it in VBA?  Thank you in advance for your reply, B.

eriiic · Answer

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

BertyBoy · Answer

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

eriiic · Answer

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

Romone13 · Answer

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

dilly213 · Answer

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 ^^

dilly213 · Answer

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

EXCEL: Concatenating a Range of Cells

11 answers

Subliminal image in a video

I'm looking for java or jar game sites.

How to unsubscribe from jm plan cul

Edit ad on leboncoin for free

Commun.file_upload.maxsize

Problem opening modal window from another page

Thursday modification test title number 3

How to change sources on a grundig universal remote?

Adobe reader saves a file instead of printing

Is tokyvideo a reliable site? what are your opinions?