if statements, searching for commma

[spirosu]

Does anyone know if there is a way to use the if function to search for comma's within a cell. For instance, in the below, using the if function, I've tried the below without success.

if(A1=",","Flag","Unflag")

It's obviously wrong...I'd greatly appreciate if someone can lend me a hand. Seems simple enough but can't figure it out.


Column A
Row 1 EMERGING MARKETS
Row 2 KINNUNEN, JUHA
Row 3 POSEN, DAVID
Row 4 HORTON, WILLIAM

[Pete_UK]

Use the FIND( ) function, as follows:

=IF(FIND(",",A1),"Flag","Unflag")

Hope this helps.

Pete

[Kevin B]

You can use the following IF, which uses the find function to look for your
comma. The find function returns an integer value indicating what character
position the character you're looking for was found in, if it is not found
find returns a zero (0).

=IF(FIND(",",A1,1)>0,"Flag","Unflag")
--
Kevin Backmann


"spirosu" wrote:

>
> Does anyone know if there is a way to use the if function to search for
> comma's within a cell. For instance, in the below, using the if
> function, I've tried the below without success.
>
> if(A1=",","Flag","Unflag")
>
> It's obviously wrong...I'd greatly appreciate if someone can lend me a
> hand. Seems simple enough but can't figure it out.
>
>
> _Column_A__
> Row 1 EMERGING MARKETS
> Row 2 KINNUNEN, JUHA
> Row 3 POSEN, DAVID
> Row 4 HORTON, WILLIAM
>
>
> --
> spirosu
> ------------------------------------------------------------------------
> spirosu's Profile: http://www.excelforum.com/member.php...fo&userid=7442
> View this thread: http://www.excelforum.com/showthread...hreadid=513762
>
>

[Dave Peterson]

=if(isnumber(search(",",a1)),"flag","unflag")

or

=if(countif(a1,"*,*")>0,"flag","unflag")

spirosu wrote:
>
> Does anyone know if there is a way to use the if function to search for
> comma's within a cell. For instance, in the below, using the if
> function, I've tried the below without success.
>
> if(A1=",","Flag","Unflag")
>
> It's obviously wrong...I'd greatly appreciate if someone can lend me a
> hand. Seems simple enough but can't figure it out.
>
> _Column_A__
> Row 1 EMERGING MARKETS
> Row 2 KINNUNEN, JUHA
> Row 3 POSEN, DAVID
> Row 4 HORTON, WILLIAM
>
> --
> spirosu
> ------------------------------------------------------------------------
> spirosu's Profile: http://www.excelforum.com/member.php...fo&userid=7442
> View this thread: http://www.excelforum.com/showthread...hreadid=513762

--

Dave Peterson

[spirosu]

Thanks for the ultra fast response everyone! Working like a charm now.

Spiro

[daddylonglegs]

Is it your aim just to establish whether the cell contains a comma, or do you want to do something with that information?

Perhaps there's a better way to achieve your ultimate objective.

btw, as you may have noticed

=FIND(",",A1) will give a #VALUE! error if A1 doesn't contain a comma

my suggestion, which returns TRUE (if there is a comma) or FALSE if not

=FIND(",",A1&",")<=LEN(A1)