Searching using wildcards only at start of Cell

[Cupid Stunt]

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.

 

[Cupid Stunt]

Are you trying to avoid yielding TRUE for numbers with one or leading spaces?

Yes.

I have an external program that I have no control over which extracts all data before a hyphen but is only looking for a number with 2 decimal places then a space, anything else it extracts is useless which includes any number with spaces and or text before it.

I don't want to just simply change any data to this format i need to highlight any data this is not in this format.

Data after the hyphen can be ignored.

 

[Rick Rothstein]

Are you trying to avoid yielding TRUE for numbers with one or leading spaces?

I was wondering that too give the formula the OP posted in Message #9 returns FALSE if there are leading spaces yet he said "...what i need but also returns True if there are spaces at the start of the cell" in Message #7.

This is the formula I came up with which seems to work (leading spaces are ignored)...

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

 

[Aladin Akyurek]

Yes.

I have an external program that I have no control over which extracts all data before a hyphen but is only looking for a number with 2 decimal places then a space, anything else it extracts is useless which includes any number with spaces and or text before it.

I don't want to just simply change any data to this format i need to highlight any data this is not in this format.

Data after the hyphen can be ignored.

Consider...

201.11 -pjkdlsg	TRUE
201.125 -pjkdlsg	FALSE
201.11 -pjkdlsg	FALSE
1 78.22 -x	FALSE
9. -jaguar	FALSE
	FALSE
22.89 -	FALSE

<COLGROUP><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5717" width=161><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

I3, copied down:

=IF(ISNUMBER(LEFT(1&H3,FIND(" -",H3)-1)+0),ISERROR(MID(H3,FIND(".",H3)+1,4)+0))

It counts 7 function calls and does not flounder on the fourth entry.

 

[Cupid Stunt]

Consider...

201.11 -pjkdlsg	TRUE
201.125 -pjkdlsg	FALSE
201.11 -pjkdlsg	FALSE
1 78.22 -x	FALSE
9. -jaguar	FALSE
	FALSE
22.89 -	FALSE

<tbody>
</tbody>

I3, copied down:

=IF(ISNUMBER(LEFT(1&H3,FIND(" -",H3)-1)+0),ISERROR(MID(H3,FIND(".",H3)+1,4)+0))

It counts 7 function calls and does not flounder on the fourth entry.

This seems to work perfectly!

Thanks so much Aladin

 

[Aladin Akyurek]

This seems to work perfectly!

Thanks so much Aladin

You are welcome.

 

[István Hirsch]

Try this 6-function formula:

=IFERROR(MID(H3,FIND(" -",H3&" -")-3,1)=".",0)+1*ISNUMBER(1*LEFT(H3,FIND(" -",H3&" -")-1))=2

 

[Aladin Akyurek]

Try this 6-function formula:

=IFERROR(MID(H3,FIND(" -",H3&" -")-3,1)=".",0)+1*ISNUMBER(1*LEFT(H3,FIND(" -",H3&" -")-1))=2

More or less a re-write: Dropping IF and substituting 1* for +0. IF version should be slightly faster though.

 

[István Hirsch]

More or less a re-write: Dropping IF and substituting 1* for +0. IF version should be slightly faster though.

Sorry, during creating a solution I consider the number of functions other posters use and I try to create a more compact solution. This procedure also ensures enough difference compared to other solutions, because, for example, a formula with 7 functions can not be the same as a formula with 6.


Important: I am in another (continental) system. Differences may be attibuted to this fact. Please check:


At me, for "201.1x -pjkdlsg" ("201,1x -pjkdlsg" in my system) your formula gives TRUE, mine gives FALSE.


Having a closer look at your formula in post #13, it seems that the ISNUMBER function checks if the first 6 characters is a number but with the inserted "1" at the beginning of H3, LEFT gives "1201,1", (or 1201.1) so ISNUMBER does not check the second decimal place, as far as I can see.