Detect a comma in a text string

[BillTony]

I need to examine a content string and determine if there is a comma present.

Seems like the easiest thing in the world, but I'm really stumped at the moment.

I will add the formula I'm currently using in the code window, along with my results.

It's the cell containing "1,000" that's giving me the problem...

Thanks in advance!

=IF(COUNTIF(A1,",")>0,"A comma is present…","No comma found.")


[TABLE="width: 253"]
 <colgroup><col width="150" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5485;"> <col width="187" style="width: 140pt; mso-width-source: userset; mso-width-alt: 6838;"> <tbody>[TR]
  [TD="width: 150, bgcolor: transparent"],[/TD]
  [TD="width: 187, bgcolor: transparent"]A comma is  present…[/TD]
 [/TR]
 [TR]
  [TD="bgcolor: transparent"]a[/TD]
  [TD="bgcolor: transparent"]No comma found.[/TD]
 [/TR]
 [TR]
  [TD="bgcolor: transparent"],[/TD]
  [TD="bgcolor: transparent"]A comma is present…[/TD]
 [/TR]
 [TR]
  [TD="bgcolor: transparent"]1,000[/TD]
  [TD="bgcolor: transparent"]No comma found.
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[RoryA]

Assuming the comma is actually in the cell and not the result of number formatting, you could use:

=IF(ISNUMBER(FIND(",",A1)),"A comma is present…","No comma found.")

 

[BillTony]

Unfortunately, the comma IS present due to number formatting - is there a way to use VBA "Instr"?

 

[jtakw]

Hi,

So since the "comma" for 1,000 does Not Actually exist, but is visible Only due to Cell formatting (Number using comma as thousand separator), are we then safe to assume Any number 1000 or higher Will have a comma?

If so:

Book1
	A	B
1	,	A comma is present
2	a	No comma found.
3	,	A comma is present
4	1,000	A comma is present
5	a,a	A comma is present
6	999	No comma found.
Sheet284

Cell Formulas
Range	Formula
B1	=IF(OR(COUNTIF(A1,"*,*"),AND(ISNUMBER(A1),A1>=1000)),"A comma is present…","No comma found.")

 

[BillTony]

Awesome!

Greatly Appreciated!

 

[jtakw]

You're welcome.