Separate Values with Comma

[nniedzielski]

I have sets of data that i can put into an engine, the engine needs the values separated with a comma. How can I copy a section of cells and when i copy them, they are copied as a set, and separated by a comma? That way i dont have to do it manually, especially when i want to copy over 50+ of them.

thank you in advance.

 

[Tom Urtis]

Just to confirm what you are doing before getting into the comma aspect of things, are you saying that you want to copy a vertical list of visible cells, and transpose them such that all values are in a single cell, showing in that single cell horizontally, and each number separated by a comma? Why would you want 50 of those lengthy numbers in one cell? That is not a common request. If you do not want them all in one cell, then what would be the need for a comma delimiter. This sounds odd.

 

[nniedzielski]

Just to confirm what you are doing before getting into the comma aspect of things, are you saying that you want to copy a vertical list of visible cells, and transpose them such that all values are in a single cell, showing in that single cell horizontally, and each number separated by a comma? Why would you want 50 of those lengthy numbers in one cell? That is not a common request. If you do not want them all in one cell, then what would be the need for a comma delimiter. This sounds odd.

yes, im aware it isnt common, and having 50 would be rare, most commonly will be 8-10, but i still dont want to have to do it manually

 

[alansidman]

If I am understanding correctly, then maybe this is what you need.

IMA Site Maintenance

 

[nniedzielski]

If I am understanding correctly, then maybe this is what you need.

IMA Site Maintenance

no, this is not what i need, i need to copy the values.

 

[nniedzielski]

Just to confirm what you are doing before getting into the comma aspect of things, are you saying that you want to copy a vertical list of visible cells, and transpose them such that all values are in a single cell, showing in that single cell horizontally, and each number separated by a comma? Why would you want 50 of those lengthy numbers in one cell? That is not a common request. If you do not want them all in one cell, then what would be the need for a comma delimiter. This sounds odd.

One extra note, i dont need the final result in excel anywhere, i just need it copied. That way i can paste it elsewhere.

 

[Tom Urtis]

This UDF will put the string in whatever cell you enter the formula =CommaMeeya() in. Modify for range because it was not clear what column your Shipment ID is in or what row the data starts in. You can add a dynamic element to the code by, for example, if Shipment ID is in column A, then you can substitute

For Each cell In Range("A2:A25").SpecialCells(12)

with

For Each cell In Range("A2:A" & cells(rows.count, 1).end(xlup).row).SpecialCells(12)

Public Function CommaMeeya() As String
Dim cell As Range, strVal As String
For Each cell In Range("A2:A25").SpecialCells(12)
strVal = strVal & cell.Value & ","
Next cell
strVal = Left(strVal, Len(strVal) - 1)
CommaMeeya = strVal
End Function

 

[nniedzielski]

This UDF will put the string in whatever cell you enter the formula =CommaMeeya() in. Modify for range because it was not clear what column your Shipment ID is in or what row the data starts in. You can add a dynamic element to the code by, for example, if Shipment ID is in column A, then you can substitute

For Each cell In Range("A2:A25").SpecialCells(12)

with

For Each cell In Range("A2:A" & cells(rows.count, 1).end(xlup).row).SpecialCells(12)

Public Function CommaMeeya() As String
Dim cell As Range, strVal As String
For Each cell In Range("A2:A25").SpecialCells(12)
strVal = strVal & cell.Value & ","
Next cell
strVal = Left(strVal, Len(strVal) - 1)
CommaMeeya = strVal
End Function

is there not a way to do this without pasting it in the excel worksheet? I really just want it copied. Thank you so much for your work so far.

 

[alansidman]

no, this is not what i need, i need to copy the values.

Once copied, what do you want to do with the data? Perhaps a mocked up solution of what you want using your first post example would be beneficial. Mind reading is not my forte.

 

[nniedzielski]

it will go into a search engine, housed in another program.