Friday, October 14, 2005

Using the SUBSTITUTE Function

Adapted from Exceltip.com. This is a good tip to be used when you have numeric data with spaces mixed in it.

For example a column of data where the thousand commas have been replaced with spaces.
1 100
2 200
3 300
etc

To add these up (including numeric data that is correctly entered) you can use an array formula utilising the SUBSTITUTE function as follows:
{=SUM(VALUE(SUBSTITUTE(A1:A4," ","")))}
where A1:A4 is your data range (with no blank cells) and the curly brackets on the end are not typed in but appear when you complete the formula using CTRL SHIFT ENTER to create the formula.


Posted by: Jethro | 10:04 AM |