Does it sound familiar to you?
If you are having this kind of issue all the time, you may want to continue reading this as I am going to show you three different approaches to fix the problem.
- Non-Formula approach
- Formula approach
- Fix the root approach
1. Non-Formula approach (if you can change the original data)
- Select the range (B2:B5) in the example
- Press Ctrl H to open “Find and Replace”
- Find what: k
- Replace with: (nothing; just leave it blank)
- Replace All
- Now all “k” are removed and your data returns to real number, i.e. SUM should work as you expect.
Just to remind to put “(k)” in the header label so that you readers know the figures are in thousand.
2. Formula approach (if you prefer not to change original data at all)
2.1) By using helper column
- In C2, input the following formula and then copy down.
=SUBSTITUTE(B2,"k","")+0
SUBSTITUTE(text, old_text, new_text, [instance_num])
It simply means “In the text, look for the old text we specify and then substitute it with the new text we want.” In our example, it looks into “1.5k“, looks for “k“, and replaces it with nothing. It returns “1.5” as a result.
SUBSTITUTE always returns text, the latter part of the formula “+0” converts the result (number stored as text) to a real number.
Now the numbers are ready for SUM. 🙂
2.2) By using array formula
I heard that, you don’t want a helper column; and you don’t want to alter the original data either. Here we go!
In B6, input the following formula
=SUMPRODUCT(SUBSTITUTE(B2:B5,"k","")+0)&"k"
The SUBSTITUTE function performs exactly the same as illustrated in helper column. Instead of feeding a single cell reference, a range (B2:B5) is fed into it.
It returns an array of {“1.5” ; “2.3” ; “4.0” ; “12.8”}.
+0 converts the array into a number {1.5;2.3;4;12.8}
SUMPRODUCT then do the simple job to add the values {1.5;2.3;4;12.8} in the array, giving 20.6 as a result.
The final step is to concatenate the “k” to the number for consistent presentation. However this act converts the final result back to text.
Tip:
As SUMPRODUCT accepts array, it is used here for those who is afraid of Ctrl Shift Enter. You may replace SUMPRODUCT with SUM; but you have to confirm the formula by Ctrl Shift Enter.
3. Fix the root approach (my preferred approach)
To prevent all these unnecessary tedious steps from happening, call the person who prepares the data, teach him/her how to format numbers into thousand in a proper way.
Don’t you think this is the best approach? 🙂
wmfexcel 
Go to number format, and in custom, update 00 k, and then it works fine… the cell shows value with k appended. but do make the calculation where needed.
LikeLike
That’s true when the value input is numeric. However in case of value being input as Text, changing format won’t work.
LikeLiked by 1 person
yeah if it is text then it wont work
LikeLike
your “consistent formatting” by using &”k” is not quite there if the answer is a whole number (ie it would not end in “.0k”)
to avoid this, use TEXT(formula,”0.0k”) around your formula
but I wholeheartedly agree with you: option 3 is by far the best (‘cos next time, you’ll get a mixture of “k” and “K” – to which the even-more-cumbersome solution is to add LOWER(…) around your range before SUBSTITUTE)
Muggles! – don’t you just love ’em?
LikeLike
Hi Jim,
You are right about the decimal issue. Thanks for your suggestion for using TEXT which works great in the example.
Can’t agree more with you on the “k” and “K” issue. It could be even worse in real life – with a mixture of “nothing”, “K” and “M”. @_@
Cheers,
LikeLike