VBA Macro code for =Concat(Transpose) formula

[Browneh89]

Hello,

I am having trouble recording a macro that separates a list of value in one cell separated by a comma.

the formula I use is =CONCAT(TRANSPOSE(ARRAY)&",") I use Shift and down arrow to select the array and F9 to convert that into separate values however the macro recorder seems not to understand these actions.

Can anyone please help me with this, would be much appreciated?

 

[Fluff]

This will put the string into C4

Sub Browneh()
   With Range("A2", Range("A" & Rows.count).End(xlUp))
      .Range("C4").Value = Join(Application.Transpose(.Value), ",")
   End With
End Sub

 

[Browneh89]

Just realised you profile is showing that you are using 2013, is that correct?
Just that 2013 doesn't have the Concat function.

Hey Fluffy, Hope you had a good new years!

No it is 365 now, we updated from 2013 last summer I forgot to update that. Sorry!

 

[Fluff]

Very quite New Year with the lockdown & pubs being closed unfortunately.

As you have 365, how about

=TEXTJOIN(",",1,A2:A100)

 

[Browneh89]

Very quite New Year with the lockdown & pubs being closed unfortunately.

As you have 365, how about

=TEXTJOIN(",",1,A2:A100)

Yup I know that feeling.

Ah that's a far easier formula to use! thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.