Dee has attended:
Excel Advanced course
Excel Intermediate course
Summing entries with numbers separated by columns
Hi,
I am working with an existing spreadsheet that has one column containing sometimes single numbers and other times multiple numbers separated with commas. Is there any way to use a sum or average function with this type of data column, or will I need to set it up a different way?
Cheers,
Dee
RE: Summing entries with numbers separated by columns
Hello Dee
Thank you for your question.
You will need to separate the numbers out into different columns/cells if you would like to be able to use all the numbers in any calculations you are doing.
Try using Text to Columns to separate out the numbers into different columns. You will need to make sure you have enough blank cells to the right of where the numbers are in the spreadsheet, so you don't end up overwriting any cells by mistake.
You might want to try the following on a couple of cells only first, so you can see how this works if you haven't used Text to Columns before.
To use Text to Columns:
1. Select the data range containing the numbers separated by commas.
2. Go to Data - Text to Columns.
3. Select Delimited at Step 1.
4. Tick Comma at Step 2.
5. Click OK from Step 3.
This should separate out the numbers into different columns for you.
Kind regards
Amanda
