Simple Excel formula

[moonhk]

Hi Reader


Simple Excel formula, How to set the formula ?
I try =IF(SEARCH("K",C2,1) >0,1000,IF(SEARCH("M",C2,1)>1,1000^2,1))
not work

Check B in 536.0B return 1
Check k in 14.1k return 1000
Check M in 41.1B return 1000000
Check G in 1.2G return 1000000000

 

[Bob Phillips]

The problem is that SEARCH does not return 0 when it finds nothing, it
returns #VALUE. Try

=IF(ISNUMBER(SEARCH("K",C2,1)),1000,IF(ISNUMBER(SEARCH("M",C2,1)),1000^2,1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Carim]

Hi,

You can use a Lookup() function :

=LOOKUP(RIGHT(A1,1),{"B","G","K","M"},{1,1000000000,1000,1000000})

HTH