converting an Alphanumeric list with commas into a number

[Citychick]

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, )?

@Riny_van_Eekelen 

[#2]

@Citychick 

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.

[#3]

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.  

[#4 · Accepted solution]

@Citychick 

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.

[#5]

Thank you that worked perfectly :)

[#6]

You are very welcome