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.
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.
