Wild card functions for IF function in Excel

Closed
Peter - Jul 25, 2009 at 10:21 AM
 Smart_GCSE_Student - Sep 2, 2010 at 08:01 AM
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
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Jul 26, 2009 at 05:12 AM
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
0
thanks heaps
0
Smart_GCSE_Student
Sep 2, 2010 at 08:01 AM
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.
0