EXCEL: Combine multiple cell string into a single cell using Macro VBA

Question

I would like to combine multiple vertical cell in excell assuming from A1:A9 as shown below:

FROM:

    {'BAKWLC001': '10.144.250.240'}
    {'BEWLC002': '10.250.32.249'}
    {'CTALRwlc01': '10.52.188.100'}
    {'CTARXwlc01': '10.20.40.100'}
    {'CTB1wlc01': '10.65.224.20'}
    {'CTBCwlc01': '10.65.194.10'}
    {'CTBGKwlc01': '10.61.248.100'}
    {'CTBIRwlc01': '10.51.200.100'}
    {'CTC0wlc01-new': '10.61.205.100'}

INTO cell A10 as shown below. Which includes Enclosing everything with an Open/Close parenthesis and separating each item with a comma and space. Additionally, The last item shown above should dynamically have the Closing Parenthesis (Assuming there are not just 9 items as shown above. And let us say it has 10, 20, 500 items etc. the last item will have the closing parenthesis).

TO:

    [{'BAKWLC001': '10.144.250.240'}, {'BEWLC002': '10.250.32.249'}, {'CTALRwlc01': '10.52.188.100'}, {'CTARXwlc01': '10.20.40.100'}, {'CTB1wlc01': '10.65.224.20'}, {'CTBCwlc01': '10.65.194.10'}, {'CTBGKwlc01': '10.61.248.100'}, {'CTBIRwlc01': '10.51.200.100'}, {'CTC0wlc01-new': '10.61.205.100'}]

I have Tried using this VBA code:

    Range("A10") = Join(Application.Transpose(Range("A1:A9")), vbLf)

But it only combines all the cells BUT with no parentheses, comma in between, and specially the flexibility of having an enclose parenthesis on whatever item is in the end of the combined cell.

Try Range("A10") = "[" & Join(Application.Transpose(Range("A1:A9")), ",") & "]" — taller
– taller, Commented Oct 22, 2024 at 4:36

Harun24hr · Accepted Answer · 2024-10-22 05:23:49Z

0

Try , comma instead of line feed vbLf. And add square bracket beginning and end of string after join.

Sub JoinText()
    Range("A10") = "[" & Join(Application.Transpose(Range("A1:A9")), ",") & "]"
End Sub

And TEXTJOIN() function for latest versions of Microsoft Excel.

="["&TEXTJOIN(",",1,A1:A9)&"]"

Edit: To ignore blank cells use Application.TextJoin().

Sub JoinText()
    'Range("A10") = "[" & Join(Application.Transpose(Range("A1:A9")), ",") & "]"
    Range("B1") = "[" & Application.TextJoin(",", 1, Range("A1:A500")) & "]"
End Sub

edited Oct 22, 2024 at 5:23

answered Oct 22, 2024 at 4:38

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Flow-Pleyah Over a year ago

Thanks! Lastly, How can i ignore cells that are blank in the range i have selected? Since i was planning the range to be dynamic. Let us say, Assuming only values A1:A9 have cell values but the range i selected was A1:A12 with cell A10 to A12 as blank? When i try this, Iam getting multiple comma at the end (one for each blank cell) . How can i ignore/bypass this blank cell on the range that i have selected and should have no comma at the end of the last item.

Harun24hr Over a year ago

@Flow-Pleyah Is there any reason to choose VBA? TEXTJOIN() if simpler solution and can easily ignore blanks. See edited answer please.

Flow-Pleyah Over a year ago

Hi @Harun24hr I plan on putting it on a button and be flexible on where the code output would be shown on different scenario if needed (incl. during presentation. i think it is cool :) when executed properly)

Harun24hr Over a year ago

@Flow-Pleyah So, please try my edited codes and let me know your feedback.

Flow-Pleyah Over a year ago

Great! code is good as expected! Thank a lot!

Collectives™ on Stack Overflow

EXCEL: Combine multiple cell string into a single cell using Macro VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related