Help to get concatenate output as per requirement

[mso3]

Hi friends,

I want to concatenate columns 'S:W' in the following order
City, Block, District, State, Country

I have to use drop down list for Country to City. These are correlated to each other using indirect function. So I want Country to city direction. in my original workbook.
I'm not getting correct output. I have shown the required output in column 'Y'. The macro is in workbook.

Any help will be highly appreciated.

Thanking you in anticipation.

[FDibbins]

This will give you the return in the order you want...
=W6&", "&V6&", "&U6&", "&T6&", "&S6

However you have some extra text in there. If these are constants, then maybe this...
=W6&", Bl."&V6&", Dis. "&U6&", "&T6&", "&S6

Insert the line break as needed

[mso3]

Thank you. So how to fix it in the following code to get require output.

Please Login or Register  to view this content.

[jindon]

This is how I read your problem.

Please Login or Register  to view this content.

[mso3]

Hi Jindon,
Thank you. It's very close to perfect. If there is no data available for Country to City then I enter a dash sign which should be replace to "" as shown in the code in post3. Just add a code to replace dash sign to "" then it will be perfect.

Thank you.

[jindon]

Post a workbook with sample data, your code is confusing.

Your desired results contains line feed in 2 places, so I must see the result for the row(s) with "-" cells.
So need to see many possible different examples and the result for them.

[mso3]

Hi Jindon,

Please see the attached workbook with sample require output shown in column 'Y'.

Thank you.

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[mso3]

Hi Jindon,

Still there is a little problem. The row 7 and 8 are incorrect. There is a blank line if there is a dash sign. In row 7 the prefix 'Dis.' is not necessary because there is no district and so I enter a dash sign. If there is a dash sign in city and block then I don't want a blank line at the top.

HTH.

[jindon]

Try this one.

Please Login or Register  to view this content.

[mso3]

Hi Jindon,
Excellent! Now it's working fine.

I appreciate you for the same.

Have a nice day.