Concatenation to combine two columns of data into a sentence

[financegrl]

Hello,
My situation is that I have two columns of data with 20,000 rows each that I'm trying to combine in some way using a formula so that any permutation of the values in columns one and two will form a cohesive sentence. Specifically, I'm pulling in data related to private equity funds and am trying to string the sector focus of the fund together with the region.

For example,

Column 1 - Sector Column 2 - Region
Energy and Renewable Energy Western Europe
Health Care, Technology, Consumer Discretionary Eastern Europe, Asia Pacific Emerging
Financials Asia Pacific Developed
Industrials, Consumer Discretionary Asia Pacific Developed, Asia Pacific Emerging

So far I have a really basic concatenation formula: ="The fund invests in the "Column 1"&" sectors"&" within"&"Column 2"&"."

This works for the majority of the rows of data - for example row one: "The fund invests in the Energy and Renewable Energy Sectors within Western Europe". However, for some of the rows the result doesn't make sense: "The fund invests in the Industrials, Consumer Discretionary sectors within the Asia Pacific Developed, Asia Pacific Emerging."

Need to write a formula that will replace commas with the word "and" where needed and add the word "regions" or "markets" at the end of phrases containing "emerging" or developed".

Any suggestions on how best to solve?

[financegrl]

To break this down into parts, the first might be to replace the last comma in the cell listing sectors with the word "and" - Asia Pacific, North America, Western Europe would then be "Asia Pacific, North America and Western Europe" -- is this part possible?

[K m]

I am having trouble following you. You should have included a sample workbook. This should give you a start

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Emerging","Emerging Markets"),"Developed", "Developed Region"),","," and",2)

What this does is replace Emerging with Emerging Markets, Developed with Developed Region and replace the second comma with "and"

[financegrl]

Apologies if this isn't explained clearly - here's an example

[K m]

Sorry, hope others can help. Ran out of time