Turning a vertical list of numbers in Excel into a string -with commas separating each one

Hello everyone,

I have a vertical list of numbers in Excel and I would like to put these numbers into a string for use in SQL server. In the image below, I have included the formula I could use to add commas. I would often just do this manually but I thought that it would probably be easy with a multi-row formula but I couldn't get this to work myself. Does anyone have an idea of how I should go about using Alteryx to do this automatically?

Thanks so much, Lucie

list of numbers.PNG

Common Use Cases

Accepted answers

Kenda

@luciebrett As long as your field is stored as a string field, you could also easily just add a Summarize tool and choose Concatenate.

All comments

vishwa_0308

Hi @luciebrett

You can do this in alteryx, please refer the attached screenshot.

First convert the numbers using select tool to string.

vishwa_0308

Hi @luciebrett,

Earlier i thought you want to add current row and row+1 but hope you want all your rows into a column separated by "," Then in this case you can use crosstab tool.

1. Convert your Field(Number) into a string type.

2. create one field(Group) with same value ( To grouping all values) or use if you have one in your data.

Please refer the screen below:

3. Default separator is "," itself.

Kenda

@luciebrett As long as your field is stored as a string field, you could also easily just add a Summarize tool and choose Concatenate.

jdunkerley79

I would use a select tool to convert to a string then summarise to concatenate

Sample attached

Concatenate.yxmd

luciebrett

Thanks @Kenda, super quick and easy! I have never used the concatenate option in summarize before!

luciebrett

Thanks @jdunkerley79, perfect solution - thanks!

luciebrett

Thanks @vishwa_0308, I must have done something wrong as when I tried to do this I ended up just transposing the numbers one to each column. Thanks for your help but I have managed to solve with the other responses. Have a great day!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703