Anyone know of a way to average comma separated values within a cell without using text to columns

fearley 

Without knowing a bit more, I can only brainstorm with you.

1. Is this a one-time matter, or are you anticipating this as a recurring need, for some reason or other? [That could help determine what resolution is most suitable]
2. How many of those comma separated values are there in the cell?
3. How many cells have those comma separated values?
4. Is there truly not some part of the spreadsheet where you could avoid that overwriting problem?

• If it's a one-time event,  can you simply export the cell(s) in question as a csv file, and then import it and do the separating in that imported separate sheet as you would upon opening any other CSV file. Do your average calc there and bring the value into your workbook where you were wanting to avoid the overwriting.
• You can always use LEFT, MID, RIGHT in conjunction with FIND to do your own parsing, I suppose, but the results would need to be placed somewhere....and I suspect you're aware of the text parsing abilities anyway so I'm not telling you anything new.
• If it's going to be a recurring situation, you might be able to write a VBA routine (I don't do those, so I'm not sure)

Tell us a bit more of the situation you find yourself in, why text to column won't work for some place in the existing sheet.