excel learner

asked on

# formulae which will identify the presence of a dot (.)

Dear experts

Need a formulae which will identify the presence of a dot (.) in a text string and if the condition is met, the formulae will return true.

Example (cell A2)

Xyz.xls Formulae should return ‘True’

The idea is that if the result of the find/search formulae is true, then i would like to put “”, no character, else put the valuein the cell A2.

Kindly provide the formulae for the same.

Thank you

Need a formulae which will identify the presence of a dot (.) in a text string and if the condition is met, the formulae will return true.

Example (cell A2)

Xyz.xls Formulae should return ‘True’

The idea is that if the result of the find/search formulae is true, then i would like to put “”, no character, else put the valuein the cell A2.

Kindly provide the formulae for the same.

Thank you

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

=IF(ISERROR(FIND(".",A2,1)),FALSE,TRUE)

I guess you want A1 rather than F1.

Shortened it to

=IFERROR(IF(FIND(".",A1,1),"True"),"False")

Sid

=IFERROR(IF(FIND(".",A1,1)

Sid

What you probably want to do is use the following:

`=NOT(ISERROR(FIND(".",A2)))`

IFERROR only available in Excel 2007 and later!

Runrigger: Yes you are right.

Excellearner: Which Excel version are you using?

Sid

Excellearner: Which Excel version are you using?

Sid

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

....but I should say that StephenJR,s approach is first and best IMO

barry

barry

If you just want True/False, then:

=ISNUMBER(FIND(".",A2))

=ISNUMBER(FIND(".",A2))

Missed the last part of the Q, so I agree Stephen's does exactly what you want. :)

Barry/Rory, kindly advise IYO

is?

=IF(ISNUMBER(FIND(".",F1)),F1,"")

better than?

=IF(ISERROR(FIND(".",F1)),"",F1)

clearly I have slightly modified my formula @34863823 and I agree that Stephen's response is more complete given the later part of the question, I just wondered if ISNUMBER is better/more efficient than ISERROR?

is?

=IF(ISNUMBER(FIND(".",F1))

better than?

=IF(ISERROR(FIND(".",F1)),

clearly I have slightly modified my formula @34863823 and I agree that Stephen's response is more complete given the later part of the question, I just wondered if ISNUMBER is better/more efficient than ISERROR?

I doubt if there's much in it really, Runrigger. I think I have a slight preference for ISNUMBER because it's a positive test......but that's probably just me.

Otherwise which one you use might depend on how you need to structure any other parts of the formula. In this case, in a simple IF function, obviously you can just swap the TRUE/FALSE arguments as per your example so it probably doesn't matter....

regards, barry

Otherwise which one you use might depend on how you need to structure any other parts of the formula. In this case, in a simple IF function, obviously you can just swap the TRUE/FALSE arguments as per your example so it probably doesn't matter....

regards, barry

Thanks for the feed back Barry, I didn't think there would be much in it, but a positive test I guess is the better approach!

rgds

Dave

rgds

Dave