Formula for .. IF Partial Match = PASTE, IF NO MATCH = BLANK

Cmoreno89 

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.

Cmoreno89 

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.

"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."

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.

Cmoreno89 

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.

Cmoreno89 

We started from that. If K28 has "2" initial formula returned D5 into E5. If no "*2*" in D5, when blank.

Answer was that's not correct, we shall take into account slashes and spaces. So the question is where are slashes and spaces, directly in K28 of we shall around "2" with them in formula.

"Main question what is partial match and how you define patterns in filter range. As "280" and we assume space/slash shall be around; or as " 280/" and we don't care what is about the pattern within text in D5; or that could be both. "

The K range has some of "280", "12/" , " 101/", ... it varies. Can't wildcards be used in conjunction with the K range? Like...

"*" & $K$28:$K$400 & "*" ?

I just dont know which formula will effectively search every cell in that K range?

$D5 will constantly change. But the column K range will not. I need $D5 to be copied over to $E5 only if it's a partial match to any of the cells in the K column range. The formula has to check every cell in K range. So lets say K28 has "123"... if D5 has any *123*, then $D5 will appear in $E5. If not, then $E5 will be left blank.

Is this making sense?

Cmoreno89 

Main question what is partial match and how you define patterns in filter range. As "280" and we assume space/slash shall be around; or as " 280/" and we don't care what is about the pattern within text in D5;  or that could be both. 

There are two durations, before and after, correct. But all those formulas are already completed. That's not the issue.

The only thing I need is a formula to check if ANY cell in the K range has a partial match to $D5. Essentially I am filtering $D5 with the list of numbers slashes and spaces from the K range ($K$28:$K$400). If any cell in the K range has a partial match with $D5, then $D5 will appear in $E5. This formula will be entered into the $E5 cell.

Cmoreno89 

Yes, that makes difference. Looks like

Do I understood correctly you have two durations, before and after. Filter works on any of it if we have just number. If number between space and slash, we filter before. If number between slash and comma, we filter after. Or we don't need comma? - after the slash till end of the text.  any other combinations?

In initial sample there are only numbers, not very clear how slash and space are used in combination with them.

"@Cmoreno89

It returns D5 if there are blank cells in the filter range, is that the case?"

No. I think you have it all backwards. I'm trying to filter the $D5 cell using the K range previously mentioned ($K$28:$K$400). The K range only has numbers, slashes "/" and spaces " ". No letters. If that makes any difference?

I'm essentially trying to only have certain date durations appear in $E5 that are already listed in the K range. And if $D5 does not contain any of date durations in the K range, then I would like $E5 to remain empty. Essentially we are trying to go through every cell in the K range to see if there is any partial match to $D5. If there is, I want $D5 simply copied over to $E5, and if there are zero partial matches, I would like $E5 to remain empty. That is all. Thanks again for your time, hopefully you or someome else can come along and figure this one out.

Cmoreno89 

It returns D5 if there are blank cells in the filter range, is that the case?

Cmoreno89 

So, " 285" has the space before "2", is that the case? Or it shall be between " " and/or "/" as separators. If "etc." means any other separators could exists, which ones?

Correction: Thanks but that formula isn't filtering properly. It's still allowing $D5 to appear in $E5 despite no partial match. I only want $D5 copied over into $E5 ** if any cell in the K range matches any part of the $D5 cell

Yes but that was just for this test example. In my workbook, I do not have a "2", I would only add a "2" if it included a space before or after the like " 2" or "2 " or" 2/" or "/2 " etc.

Thanks but that formula isn't filtering properly. It's still allowing $D5 to appear in $E5 despite no partial match. I only want $D5 copied over into $D5 if any cell in the K range matches any part of the $D5 cell

Cmoreno89 

In E5

=IF( SUM( COUNTIFS(D5, "*" & $K$28:$K$33 & "*" ) ), D5, "" )

"2" matches one the cells in K-range