Previous Topic Next Topic

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
Related:

3 responses

Answer 1 / 3
Stef
Sep 7, 2009 at 02:13 AM
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
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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
Peter
Jul 26, 2009 at 06:27 AM
thanks heaps
0
Answer 3 / 3
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