Link to home
Start Free TrialLog in
Avatar of excel learner
excel learnerFlag for United Kingdom of Great Britain and Northern Ireland

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
SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
=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
What you probably want to do is use the following:
=NOT(ISERROR(FIND(".",A2)))

Open in new window

IFERROR only available in Excel 2007 and later!
Runrigger: Yes you are right.
Excellearner: Which Excel version are you using?

Sid
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
....but I should say that StephenJR,s approach is first and best IMO

barry
If you just want True/False, then:
=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?
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
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