Wild card functions for IF function in ExcelClosed

Question

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

venkat1926 · Answer

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

Stef · Answer

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.

Smart_GCSE_Student · Answer

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.

Wild card functions for IF function in Excel

3 responses

Similar discussions

Subscribe To Our Newsletter!