Forum Discussion
Formula for .. IF Partial Match = PASTE, IF NO MATCH = BLANK
Okay, if in filter range we could have "12", "12/", " 12" and in D5 text like "... 112/253", no one shall work. Only " 112/" if exists.
With formula we may remove all slashes and spaces around the numbers in filter range and check only for cleaned numbers with added by formula combination of slash/space around. If so it's even better to use only numbers in filter range.
The only question with that shall we differentiate before and after. If not when we may use just number 12. If " 12/" and "/12," shall give different result when above doesn't work.
With formula we may remove all slashes and spaces around the numbers in filter range and check only for cleaned numbers with added by formula combination of slash/space around. If so it's even better to use only numbers in filter range.
The only question with that shall we differentiate before and after. If not when we may use just number 12. If " 12/" and "/12," shall give different result when above doesn't work."
The K range cannot be changed because the slashes and spaces are there for filtering purposes. They are part of the system I'm trying to implement. Surely a formula exists to compare all characters for partial matchimg purposes? Unfortunately I have an intermediate skill level with excel. This formula requires expert knowledge of course.
Unfortunately I have no Android tablet around and not sure which functionality is available on it. Also you didn't confirm formula for the birthday in one of previous posts works on it. Thus let use formulae which shall work on any Excel.
By the way, I'm not sure you are in US locale or not, other words "My Birthday: 8/11/1993" means Aug 11or Nov 08. In formula is the latest.
Back to filter. I'd suggest to add two helper cells, for before and after. They could be at any place of the workbook, here
they are in C7 and C8. With that formula in E5 could be
=IF( OR( ISNUMBER( MATCH($C$7,$E$9:$E$200, 0) ), ISNUMBER( MATCH(" " & $C$7 & "/",$E$9:$E$200, 0) ), ISNUMBER( MATCH("/" & $C$8 & ",",$E$9:$E$200, 0) ), ISNUMBER( MATCH($C$8,$E$9:$E$200, 0) ) ), $D$5, "" )You may add any other patterns for filtering. In above we assume full number itself, or number between space and slash; or number between slash and comma.
- Well, what would the formula be then with COUNTIF? I'm on an android tablet version of excel with the most up-to-date version from the play store.
Formula exists, that's COUNTIF on "*" & range & "*". If we exclude dynamic array functions which could not work on mobile. Plus that's the difference between versions on Android and iOS. COUNTIF shall work on any version.
The question is mainly in logic of patterns for filtering.