Need a formula to indicate string that only have on full stop in decimal point in them

**john dalton** · 02-08-2016, 07:22 AM

Hi I need a formula that would look at a text and numbers string and return a 1 if the string contained a "." in it.

For example

String 1 john Samuels 27/12/1969 284.00

String 2 john Richardson 28/04/1971 287.0 286.0

The first string would return one, the second string would return a blank cell.

Cheers

J

**TMS** · 02-08-2016, 07:28 AM

This will count the full stops in a cell:

Formula:

Please Login or Register  to view this content.

Do with it as you will.

Regards, TMS

**samba_ravi** · 02-08-2016, 07:29 AM

Will you pls attach a sample excel file with desired result

**TMS** · 02-08-2016, 07:54 AM

Thanks for the rep.

**Tony Valko** · 02-08-2016, 08:15 AM

Try this...

Data Range

	A	B
1	john Samuels 27/12/1969 284.00	1
2	john Richardson 28/04/1971 287.0 286.0
3		------

This formula entered in B1 and copied down:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=1,1,"")

Need a formula to indicate string that only have on full stop in decimal point in them

LinkBack

Thread Tools

Rate This Thread

Display

Need a formula to indicate string that only have on full stop in decimal point in them

Re: Need a formula to indicate string that only have on full stop in decimal point in them

Re: Need a formula to indicate string that only have on full stop in decimal point in them

Re: Need a formula to indicate string that only have on full stop in decimal point in them

Re: Need a formula to indicate string that only have on full stop in decimal point in them

Thread Information

Users Browsing this Thread

Similar Threads

SUMIF Formula that stops at a certain point and does not include the stop point

Minor formatting issue: allow capturing a (.) full-stop as decimal

Add decimal point to string

Formula to separate a decimal number after the decimal point

Decimal Point Formula

[SOLVED] how do I stop excel putting a space after a decimal point

Bookmarks

Bookmarks

Posting Permissions