Replacing Filtered Data with Concatenated Results

**easbrrx** · 12-17-2013, 10:57 AM

Hi,

I'm new to using functions in excel, and I'm having trouble with what I believe should be a simple fix.

I have a pre-populated excel report which populates information in several columns. Think of it like this:

F--------G-------H----------------I
1 This is a sample of what I'm dealing with
2 There are up to hundreds of rows of information
3 I only need about twenty to actually show up
4 This is the row I will use as a sample

First, I use the filter to get rid of unneeded rows (in this case 1-3). I do not need the other text at all, it could be deleted.

Then, I need to concatenate the info, so I use the formula =CONCATENATE($F1, " ", $G1, " ", $H1, " ", $I1), and copy it to an entire column, creating this:

F------G-------H----------------I-------------------------K
4 This is the row I will use as a sample --------------------- This is the row I will use as a sample

So now, I want to replace the original information with the concatenated information. This is the formula I tried: = REPLACE($F2 & $G2 &$H2 & $I2, 1, 1024, $K2)

It populates the concatenated info to the cell in which I placed the formula and does not replace the info in the other cells. [Edit]: I should explain that I did 1024 because that was the maximum number of letters populated per cell.

I'm hoping someone on this forum could help me learn how to fix this. I am trying to learn about functions, so I don't want just the answer. I'd like to learn from my mistakes!

Thank you!

**Kaper** · 12-18-2013, 04:20 AM

Hi,
With build-in functions, there is no way to repace content of any cell.
If I understand you well, you have already the result in K column.
1) How to:
So now shall select all column K, copy it, and PasteSpecial as Values into column F.
Now you can Delete no longer needed column K, as well as columns G:I
2) REPLACE function:
See how it works (before column K is deleted) try in L2:

Please Login or Register  to view this content.

Then probably explanation in help file will be more clear
3) Filtering:
If you want to get rid of unwanted text - use filter with opposite criteria (to have only unwanted text visibe.
Then select all visible rows with data (this unwanted). I mean all rows - the row numbers on the left shall have dark background.
Delete selected rows and switch off filter. Only interesting data shall remain. No empty lines etc.

Best Regards,