Forum Discussion
converting an Alphanumeric list with commas into a number
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.
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.
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.