Searching using wildcards only at start of Cell

Cupid Stunt said:

Hi,

Please could someone help

I need a formula that will return true if start of a cell contains any number with 2 decimal places then a space then a hyphen. the cell could also contain any other text after this.

i.e. "201.11 -pjkdlsg" would return True

but "sdjklg 201.11 -ppsdfg" would return False

The 201.11 - must be at the start of the cell.

I am currently using IF(COUNT(SEARCH("?.?? -*",H3))>0,"false","true") but this searches the whole cell and returns true is 201.11 - is anywhere in the cell not just the start.

Click to expand...

Aladin Akyurek said:

Try...

=ISNUMBER(LEFT(H3,FIND(" ",H3))+0)

This assumes a space after an admmissible number at the start of a string.

Click to expand...

Cupid Stunt said:

So close!!!

Problem i have is formula must only return True if the number has a hyphen after the space, this returns true if the hyphen is missing

Click to expand...

Aladin Akyurek said:

Does this...

=ISNUMBER(LEFT(H3,FIND("-",H3)-1)+0)

help?

Click to expand...

JoeMo said:

This returns False if not exactly 2 decimal places.
Sheet10

	A	B
1	201.112 -pjkdlsg	FALSE
2	djklg 201.11 -ppsdfg	FALSE

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:131px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =AND(ISNUMBER(0+LEFT(A1,SEARCH(".",A1)-1)),LEN(MID(A1,SEARCH(".",A1),SEARCH(" -",A1)-SEARCH(".",A1)-1))=2) B2 =AND(ISNUMBER(0+LEFT(A2,SEARCH(".",A2)-1)),LEN(MID(A2,SEARCH(".",A2),SEARCH(" -",A2)-SEARCH(".",A2)-1))=2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Click to expand...

Cupid Stunt said:

Combined the two and looks like it's done the trick!

=AND(ISNUMBER(LEFT(H3,FIND(" ",H3))+0),LEN(MID(H3,SEARCH(".",H3),SEARCH(" -",H3)-SEARCH(".",H3)-1))=2)

Thanks so much Joe & Aladin

Click to expand...