Search function, for specific text, but need to match if it starts with

[andy.k]

Please help.

Have formula here that searching for string text, but need it to search only if it starts with that text and not anywhere where else.

Here is the formula:
IF(ISERROR(SEARCH("AMP",A4)),"",A4)

Its working, but need it to look only if it starts with "AMP".

Thanks!

[TMS]

Maybe:

=IF(LEFT(A4,LEN("AMP"))="AMP",A4,"")


Regards, TMS

[daddylonglegs]

You could also use COUNTIF with a wildcard, e.g.

=IF(COUNTIF(A4,"Amp*"),A4,"")

Note: that isn't case-sensitive - does it need to be? This version is case-sensitive

=IF(EXACT(LEFT(A4,3),"AMP"),A4,"")

[andy.k]

Thanks for the great help here... I'm moving forward!

So, here is what I'm doing.

First, I need to clean data so it does start with "AMP", so for that I use following:

RIGHT(A4,LEN(A4)-4) - This gives me most of the data that starts with AMP

Now, I want to display data that starts with AMP, so,

=IF(COUNTIF(A4,"Amp*"),A4,"") - works great (don't need it to be case sensitive

Now, how do I combine both formulas into one?

[daddylonglegs]

So "AMP" is actually characters 5, 6 and 7 in your original data? Try this formula

=IF(COUNTIF(A4,"????Amp*"),A4,"")

[andy.k]

It works, but I still need to clean it up and remove first 4 characters, that is why I was using: RIGHT(A4,LEN(A4)-4)

So, now need to combin either one of your formulas

[daddylonglegs]

OK, I'd use REPLACE to remove the first 4 characters so that would be

=IF(COUNTIF(A4,"????Amp*"),REPLACE(A4,1,4,""),"")

or you could use MID like this

=IF(MID(A4,5,3)="Amp",REPLACE(A4,1,4,""),"")

[andy.k]

It worked! Thank you for your help!