Do we have like Formula in excel like like vba statement?

[pedie]

Hi, do we have like Formula in excel like the "like" vba statement?

 

[Aladin Akyurek]

Depends on what is needed... Care to describe that?

 

[pedie]

I have data [text] like this in col a

MrExcel Online Store Pedie NZ Dept 2X V2
I use vlookup to get data...but at times the there are more spaces being added or reduced though Pedie NZ part always remains the same...I just don't know what to do...I want the vlookup to match [likely] but accuately only Pedie NZ part...


Thanks again for hepling.

 

[Aladin Akyurek]

I have data [text] like this in col a

MrExcel Online Store Pedie NZ Dept 2X V2
I use vlookup to get data...but at times the there are more spaces being added or reduced though Pedie NZ part always remains the same...I just don't know what to do...I want the vlookup to match [likely] but accuately only Pedie NZ part...


Thanks again for hepling.

You could try something like...

=VLOOKUP("*"&"Pedie"&"*"&"NZ"&"*",Table,Col,0)

 

[pedie]

Alladin thanks a lot. But I have a long list of names in this way
This work but would consume lots of time...will there be other way
Like to vlookup(A1,....etc?

 

[Aladin Akyurek]

Alladin thanks a lot. But I have a long list of names in this way
This work but would consume lots of time...will there be other way
Like to vlookup(A1,....etc?

The suggestion involves manipulating the look up value, not the table.
We can make that a bit easier though...

A1: Pedie NZ

B1:

=VLOOKUP(SUBSTITUTE(A1," ","*"),$E$2:$F$400,2,0)

Note that this would match occurrences in E2:E100 like "Pedie in NZ", "PedieNZ", "Pedie NZ", etc as wel as "Pedie NZ".

 

[pedie]

Aladin, once again thanks alot...works great!

 

[Aladin Akyurek]

Aladin, once again thanks alot...works great!

You are welcome. Thanks for providing feedback.

 

[buachaille]

Since "like" exists in VBA, why not use it to create your own ?

Function IsLike(CellToMatch, Pattern) As Boolean
IsLike = CellToMatch Like Pattern
End Function

and call it like (no pun intended)

=IsLike(B9,"*XX*A6*")

or similar