Search results

You can overcome the limitation of the length by replacing 100 with len(A2)

Hi Xiq, The limitation is not for number of words, but the length of the words.

Explanation for the above formula: =IF(LOOKUP(1,MMULT(IFERROR(N(ROUND(MOD(SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1))-1)*100+1, 100))&".1",{".",""},""),{",",""},""),1),1)={0,0.1}),0),{1;-1})),"Bad","Good")...

You are welcome.

Hi Rediska, Your second set of samples and rules seem to contradict your first set of samples. I suggest you upload a file with representative data, along with expected outcomes for each row. I interpreted your requirements as follows: Anything of the format XX999XX is "good" Anything of the...

Hello Rediska, So, if you find a standalone number (example: 235), that would not be an error? It would be an error only if it has a comma or decimal point (before, in the middle, or after)? Please confirm.

Hi, You are welcome. Though you did not ask for it, here is a brief explanation of the first formula: It is a technique I learned from Lori. =IF(LOOKUP(1,N(FIND(" "&MID(TRIM(A2), ROW(OFFSET(A$1,,,LEN(TRIM(A2)))), MMULT(FIND(" ",{""," "}&TRIM(A2)&"...

Hi Rediska, An array is just a collection of items... The only difference between a single item and an array is that an operation is applied to all of the items in that collection. FIND is needed to traverse a string. (You could also use SEARCH.) INDEX and MATCH works with discrete...

Hi Bob, Interesting comment about monitoring of posts.... Chandoo.org is indeed addictive! Hi Rediska, In the third formula, the first argument to FIND uses an old technique that I believe is credited to Rick Rothstein. It expands each space found in the original string into 100 spaces, and...

Hi Rediska, Thanks for the feedback. Glad to help. Hi Luke, We all learn from each other. (I have certainly learned a lot from you.) Regards, -Sajan.

Hi Lori, That is a clever use of MODE.MULT to filter out stuff! With this challenge alone, I have developed a better appreciation for LOOKUP and MODE! Following your idea, I am including the other two formulas using MODE.MULT for completeness: Unique...

Hi, The following is another approach for a string in A2: =IF(ISNA(MODE(FIND(" "&TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1))-1)*100+1, 100))&" ", " "&TRIM(A2)&" "))), "No Dupes", "Dupes") Cheers, Sajan.

When you say "type the column value", do you mean the column number, cell reference (like CT8) or something else?

Hi, I noticed that the previous formula would result in some false positives under some conditions. As such, try the following regular formula for a value in cell A2: =IF(LOOKUP(1,N(FIND(" "&MID(TRIM(A2), ROW(OFFSET(A$1,,,LEN(TRIM(A2)))), MMULT(FIND(" ",{""," "}&TRIM(A2)&"...

What I would like to know is how Chandoo conducted that experiment? Hopefully, he used copious amounts of shaving gel!!

Hello, You could try the following array formula for a value in cell A2: =IF(SUM(N(IFERROR(FIND(" "&MID(A2,ROW(OFFSET(A$1,,,LEN(A2))), MMULT(FIND(" ", {""," "} &A2&" ",ROW(OFFSET(A$1,,,LEN(A2)))),{1;-1}))&" ", " "&A2&" "), LEN(A2)+1)<ROW(OFFSET(A$1,,,LEN(A2)))))>0, "Dupes", "All good") enter...

Hi, Can you add a couple of rows of manually calculated cells? I am not completely clear on what you are asking. -Sajan.

Hi Ozbear, Yes, please upload the worksheet. -Sajan.

Hi Jeff, One of my favorite blog posts is the following by Colin Legg: http://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/ -Sajan.

Hi Jeff, In my opinion, for novices (and others), a strategy that seems to be working well, based on the posts in this forum, is to just ask for help, and experts readily jump in and solve the problems for them! (No need to bother with macros and stuff themselves!) One does not even have...

Hi Maggie, Can you describe what you are attempting with the following segment? (I shortened the range to 100 to debug) N(OFFSET(INDIRECT("'"&$R$1:$Y$1&"'!A2:A100"),ROW(INDIRECT("1:100"))-1,,)) Can you describe the problem you are looking to solve overall? -Sajan.

Hello Suma, Welcome to the forum! When you say "auto-capture", do you mean a quick way to enter the time? If so, go to a cell and press Ctrl + Colon ":" key (i.e. you will need to press Ctrl + Shift + : since on most keyboards, the : is only available upon shifting. If you are looking for...

Hi Rediska, Glad you found a solution! Thanks for the feedback. Glad to help! Come back any time! -Sajan.

Hi Maggie, Welcome to the forum! It might be better if we help with the problem you are looking to solve, instead of repairing the formula above. Towards that end, please describe what you are trying to do, and we might be able to offer suggestions or solutions. Also, it usually helps to...

What I meant about global settings is that in the string "[]{}(),0123456789", the comma needs to be there if your input data will have commas. (For all other commas, please replace it with the delimiter that is valid for your version of Excel.)