Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify text nums in scrambled fashion
All nums are text nums
In A1:A3 would be 3 "winning" 4-digit text numbers, eg: 0044 1234 4233 In C1 down will be input 4 digit text nums such as: 0440 4120 2343 3324 etc If the text nums in C1 down happen to contain the same 4 digits as in any of the 3 winners in A1:A3, then to indicate an "x" in adjacent col D (otherwise just leave it blank) For the sample data, the results in col D would be: 0440 - x 4120 2343 - x 3324 - x I'm game for any formula, udf or vba solution which can do the above Insights welcomed. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify text nums in scrambled fashion
Try this...
Assuming there are no empty cells within the range A1:A3. In the formula, Nums refers to the range A$1:A$3. Entered in D1 and copied down as needed: =IF(FREQUENCY(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(Nums,MID(C1,1,1),"",1),MID(C1,2,1),"", 1),MID(C1,3,1),"",1),MID(C1,4,1),"",1)),0),"x","") -- Biff Microsoft Excel MVP "Max" wrote in message ... All nums are text nums In A1:A3 would be 3 "winning" 4-digit text numbers, eg: 0044 1234 4233 In C1 down will be input 4 digit text nums such as: 0440 4120 2343 3324 etc If the text nums in C1 down happen to contain the same 4 digits as in any of the 3 winners in A1:A3, then to indicate an "x" in adjacent col D (otherwise just leave it blank) For the sample data, the results in col D would be: 0440 - x 4120 2343 - x 3324 - x I'm game for any formula, udf or vba solution which can do the above Insights welcomed. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify text nums in scrambled fashion
Brilliant, Biff. Works great
Many thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify text nums in scrambled fashion
You're welcome, Max.
Thanks for the feedback! -- Biff Microsoft Excel MVP "Max" wrote in message ... Brilliant, Biff. Works great Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you turn on the count nums when filtering in excel? | Excel Discussion (Misc queries) | |||
Combining words and nums. | Excel Worksheet Functions | |||
killing empty spaces in unusall fashion .. | New Users to Excel | |||
Have column of nums, need qty of each? | Excel Worksheet Functions | |||
When I import a csv file text that contained a + gets scrambled | Excel Discussion (Misc queries) |