Automatically concatenate text in cells containing specific text

[cmaunder]

Hi all,

I have what I hope is a simple problem to resolve.

I have a spreadsheet which contains rows called "Sample ID" and "Asbestos Content" (see attached example).

Where a cell in the "Sample ID" column contains the words "Visually Consistent with...", I need to
a) add parentheses around the Asbestos Content cell beside it in the same row,
b) concatenate the text in the "Sample ID" and "Asbestos Content" cells in the same row into the "Asbestos Content" cell, with a hard return separating the concatenated text, and
c) Insert the text string "Not Sampled" in the "Sample ID" cell from the row being altered.

This isn't too hard to do manually using the concatenate formula, but not being able to automate the process over hundreds of rows in some cases is driving me nuts.

I've attached both a large dataset in need of this automation, and an example spreadsheet with "before and after" examples. The latter contains two pairs of two rows highlighted in blue and in purple. In each pair of rows the top is what I have currently and the bottom is what I want these cells to all look like.

Any help is greatly appreciated.

[Leith Ross]

Hello cmaunder,

This macro has been added to the attached workbook. There is a button on the worksheet to run it.

Please Login or Register  to view this content.

[cmaunder]

Sorry for the late reply Leith, but thanks very much. Works like a charm, and I've learned a few things in the process. Thanks again!