Ask Your Question

How to have multiple values on a cell from a selection list comma seperated??

asked 2020-06-29 10:32:57 +0200

Akis gravatar image

I have made a selection list with text-values. I want to choose more than one value from the selection list on the same cell and be comma seperated. Is there any way? Thanks

edit retag flag offensive close merge delete


How did you make your "selection list"?
Did you use >Data>Validity> ? What else?
Generally prefer the semicolon as a list separator. It will help to avoid ambiguity othewise easily occurring as soon as some of the strings represent numbers.
As far as I know there is no ready-made toll for what you (supposedly) want.

Lupp gravatar imageLupp ( 2020-06-29 13:15:31 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-06-29 13:44:49 +0200

Lupp gravatar image

updated 2020-06-30 11:50:55 +0200

"Any way" will exist next to always concerning the manipulation of contents in spreadsheets.
Alas! There often isn't a ready-made tool.

If I understood (in principle) correctly what you want to achieve,my attached example may give you a clue how to do it with the help of user code ("macro").

=== Edit 2020-06-30 about 09:40 UTC ===
Don't use the above attached solution. It's buggy, and I only didn't delete it to not cover the tracks concerning the discussion below. After all an analysis of the bug is instructive in itself for those intending to actively use the ASPI API themselves.

The fixed version of the demo now:

edit flag offensive delete link more


@Lupp, Find&Replace a few typo error in your file and delete this (my) comment: stings&strings; D9&; D20:E20&D21:E21; fous&focus.

LeroyG gravatar imageLeroyG ( 2020-06-29 14:06:52 +0200 )edit

Thank you very much for your help.

When trying to run macro at the step 7 it says "BASIC runtime error. Property or method not found: RangeAddress"

What can i do?

Akis gravatar imageAkis ( 2020-06-30 08:29:04 +0200 )edit

In fact the demo I had attached contained a "bug": It assumed an actual multi-selection of source ranges without justification, and fails if only exactly one range is selected as source. The issue is fixed in the new demo I will attach now to my answer.
The bug you found should only occur if exactly one cell is selected as source.
In this case a "slightly" problematic way of the API to handle the access to its services spoils the situation: The cell is not clearly distinguished from the text it's containing. Therefore the For Each loop is supposed to be about portions of the text in this case (no RangeAddress then, of course) instead of about contents of a SheetCellRanges object.
This bug wasn't consciously af´ddressed by my mentioned fix, but is fortunately also fixed by it.

Lupp gravatar imageLupp ( 2020-06-30 11:28:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-06-29 10:32:57 +0200

Seen: 34 times

Last updated: Jun 30