Differentiating a text string

[anthony Upward]

Hi,

I have a company phone bill listed as so:

Number call type
duration
123456789 text message (sms)
00:00:01
123456789 international text message (sms)
00:00:01

The list goes on with many numbers and different call types, what I hope to
achieve is

If call type = text message (sms) then 1 but at the moment my formula gives
a value of 1 to the international category as well as it has the text
message (sms) string. I would like to know how to specify an exact or
absolute text string to search for please?

My current formula is
=IF(ISNUMBER(SEARCH($Z$41,$H$2,1)),1,"Not SMS")

Cheers for any comments

 

[Pete_UK]

Well, ISNUMBER(SEARCH ... is looking for the text in Z41 being
contained anywhere within H2. You could use:

=IF($Z$41=H2,1,IF($Z$42=H2,2,"Not SMS"))

if you have "international text message (sms)" (without the quotes) in
Z42, and you want this to have a category of 2.

However, it would be better to have all your categories listed and
have the code against them in an adjacent column, and then you could
use a VLOOKUP formula to get the appropriate codes.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Hi,

I have a company phone bill listed as so:

Number call type
duration
123456789 text message (sms)
00:00:01
123456789 international text message (sms)
00:00:01

The list goes on with many numbers and different call types, what I hope to
achieve is

If call type = text message (sms) then 1 but at the moment my formula gives
a value of 1 to the international category as well as it has the text
message (sms) string. I would like to know how to specify an exact or
absolute text string to search for please?

My current formula is
=IF(ISNUMBER(SEARCH($Z$41,$H$2,1)),1,"Not SMS")

Cheers for any comments

I'm assuming that Z41 contains "text message (sms)" (without the quotes),

So how about:

=IF(EXACT($Z$41,$H$2),1,"Not SMS")

--ron