Ignore Dashes In Find Statement

[dar149]

I have a spreadsheet where, through code, I find a particular part number and return information based on the result of the find statement. If I take the dashes out of the part number I'm searching for, is there a way to code the search so that dashes are ignored in the data I'm searching? For example, if I'm searching for plt1, I want it to find plt-1. I haven't been able to find a way to do that. We don't want to take the dashes out of the data being searched.

Thanks...

 

[WayneRyan]

dar,

This code will take a Social Security Number and subtract one.

ex: 123-45-6789 = 123-45-6788

Me.SomeField = Format(CLng(Mid(Me.SomeField, 1, 3)) * 1000000 + _
                      CLng(Mid(Me.SomeField, 5, 2)) * 10000 + _
                      CLng(Mid(Me.SomeField, 8, 4) - 1, "000-00-0000")

Wayne

 

[TonyJollans]

Hi dar149,

I don't think you can take the dashes out on the fly during a search but a couple of other possibilities:

Could you create another column in your sheet for part numbers without dashes which you could search (consider the worksheet SUBSTITUTE function or the VBA (2K and later) Replace function).

Or, perhaps, hold part number without dashes, but FORMAT them with dashes on all output media.

If you really want to keep the dashes I presume they have significant meaning and plt-1 would not appear as pl-t1. If this is so could you not search for plt-1 when plt1 was input? In other words change the single value you want to compare rather than every value you want to comapre against.

Enjoy,
Tony

 

[dar149]

Thanks for the info... Since I can't take out dashes on the fly during a search, I'll see if we can get by with creating a second column without dashes to do the search.