Wild card functions for IF function in Excel

[Closed]
Report
-
 Smart_GCSE_Student -
Hello,

im writing in regards of how to formulate the IF function in excel to seperate out certain characters in a particular cell. For example if a series of cells says
15k
14k
13k
12k
11k
10k

and my objective is to sum up all the values, how do i seperate the "K" out of all these cells because the 'SUM' Function doesnt seem to work because of the letter "K".
Thanks heaps,
Peter

3 replies

If the "k" is depicting 1000's, rather change the formatting in those cells to show the value in the formula bar as e.g. 15, but under cell formatting select Number, Custom and enter ####"k". This way the value of the cell is still numeric and thus calculable, but when printing or displaying it, it will show it as e.g. 15k.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
suppose A1 down is

15k
14k
13k
12k
11k
10k


in B1 copy paste this formula
=LEFT(A1,LEN(A1)-1)+0
copy B1 down
add column B

I could have had formula
=left(A1,2)+0
but this will not work if your numbers are more than 2 digits, e.g. 122k
thanks heaps

you can always do ctrl+f and replace 'k' with the number '000', that way you don't have to manually change all the ks and it makes the SUM function a piece of cake.