Concatenate non-adjacent cells in one row to one cell

[YasserKhalil]

Hello everyone
I have some data in one row (Sheet1.Rows(1)) and I need to concatenate non-adjacent cells in that row in one cell only
The new output cell would be in special format
More details:
Firm name would be first line in bold
then NAICS Codes
then Description
then empty line
then owner
then Phone & Fax & Email in one line separated by ; and space
then Website
then Address

* As for Website and Email to be underlined

I have attached output for more clarification
Thanks advanced for help

[YasserKhalil]

I have this UDF that concatenates cells (but for adjacent cells) and at the same time I need to format output so the UDF will not be the suitable solution I think

Please Login or Register  to view this content.

[YasserKhalil]

I need to deal with one row and I will do loops for other rows. Please help me

[YasserKhalil]

Any help in this topic please

[Greg M]

Hi again Yasser,

Hope you've been keeping well since our last contact

Take a look at the attached workbook and see if it does what you need.

It uses a helper Column (N) on the Company Details worksheet. This contains a formula which concatenates the various values and adds the New Line character where appropriate.

The various columns have been defined as named ranges - this makes it easier to understand the formula used.

The VBA code takes the text value from this helper column, adds the required Bold and Underline attributes, and inserts the formatted value in the appropriate cell on the Output worksheet.

The Output worksheet contains two buttons - one to clear the worksheet and one to populate it with the required data.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[YasserKhalil]

Thank you very much Mr. Greg for this fascinating solution
You are perfect.
Thanks a lot for great and wonderful help

Best Regards

[Greg M]

Hi again Yasser,

Many thanks for your feedback, for your kind words, and also for the Reputation increase - much appreciated

You're very welcome - glad I was able to help.

Best regards,

Greg M