Sep 29 2022 02:38 PM - edited Sep 29 2022 02:41 PM
Hi
I am looking at court booking data for a tennis club. The database downloads to excel and offers a field with the names of the courts being booked, all delimited by commas. it looks like this: Court 1, Court 2, Court 3, Court 4, Court 5, Court 6. Can I convert that list to 6 and this list to 2 (Court 2, Court 3, )?
Sep 29 2022 06:49 PM
I'm not @Riny_van_Eekelen but let me ask a question that he might want to ask as well.
What do you mean?
I initially thought you meant you wanted to take a single cell with contents of
Court 1, Court 2, Court 3, Court 4, Court 5, Court 6
and turn that into
Court 1
Court 2
Court 3
Court 4
Court 5
Court 6
But then your concluding sentence, "Can I convert that list to 6 and this list to 2 (Court 2, Court 3, )" made me realize you could be saying convert the first, longer, list to
6
and the second, shorter, to
2
(in fact, the title you gave to your request for help does say you want to convert the list with commas into a number)
But on top of those questions for clarification, it seems to me that there must be more going on here. If all you want to do, for example, is end up with
Court 1
Court 2
Court 3
Court 4
Court 5
Court 6
You could have typed that into your spreadsheet and been done with it.
So the primary question has to be
What else is in this database that you're downloading from your tennis club? What are you wanting to do with all of that as well? That is to say, please give us a bigger picture sense of what you're trying to accomplish.
Sep 30 2022 12:54 AM - edited Sep 30 2022 12:57 AM
Hi Mathetes
thank you for trying to help - can I upload my spreadsheet? I am trying to work out when members book at the tennis club. I have a column that shows the courts that are booked and I want to convert that to a number so I can work out how many courts are booked and at what times. I think I have uploaded the data so you can see my efforts to make a pivot table to summarise the data.
As you can see my current pivot table and the chart just show when there is a booking not also how many courts are used. So Tuesday morning looks busy but in fact Tuesday is booked often but not many courts at a time.
Sep 30 2022 07:09 AM
Solution
Here's a very quick and dirty solution. And I see that you DID want to just end with a number showing the number of courts. So my "quick and dirty"--I'm sure there are more sophisticated ways to do it--is to take advantage of the fact that there's a regular length to the wording "Court n, "--and that wording contains 9 characters.
So just take the length of the full entry in the column headed "Court(s)" and add 2 (because the final entry in any one row doesn't include the comma and space) and then divide by 9.
=(LEN(G2)+2)/9
That cleanly converts the text to a count of the number of courts occupied.
See the attached. I will let you update your Pivot Table; you clearly know how to do that.
Sep 30 2022 12:48 PM
Sep 30 2022 12:49 PM
Sep 30 2022 07:09 AM
Solution
Here's a very quick and dirty solution. And I see that you DID want to just end with a number showing the number of courts. So my "quick and dirty"--I'm sure there are more sophisticated ways to do it--is to take advantage of the fact that there's a regular length to the wording "Court n, "--and that wording contains 9 characters.
So just take the length of the full entry in the column headed "Court(s)" and add 2 (because the final entry in any one row doesn't include the comma and space) and then divide by 9.
=(LEN(G2)+2)/9
That cleanly converts the text to a count of the number of courts occupied.
See the attached. I will let you update your Pivot Table; you clearly know how to do that.