Search results

Regarding your comments about global settings... If your data will contain commas instead of semi-colons, your global settings would not matter.

Hi, There was an extra space in your first string that was throwing the second formula off. Try the following instead: =IF(SUM(N(ISNUMBER(FIND(LEFT(TRIM(MID(SUBSTITUTE(TRIM(A3)," ", REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(TRIM(A3))-LEN(SUBSTITUTE(A3,"...

I copied and pasted the formulas above into your worksheet and got the following results sample text formula #1 comments formula #2 afdsa v3434 Good Bad afdsa v3435 dsfsf Good Good afdsa 3435, verr Bad Bad afdsa v3436 Good Good afdsa v3437, 4535435 Bad Bad 345 Bad Bad sdad(455)...

Also, I noticed that the formula you are using is not the same ones I supplied. Specifically, the comma got replaced by a semi-colon in your workbook. Please correct that.

Hello Rediska, The formulas I supplied are array formulas, and need to be entered with Ctrl + Shift + Enter, instead of just the Enter key. (you can confirm that it worked by noticing {} on both sides of the formula in the formula bar.) Let me know if you are still seeing any issues. Cheers...

Hi Debraj, It is always good to look at a problem from different perspectives...! By the way, you should also consider the N() function. Cheers, Sajan.

Hi again, I like to complicate things unnecessarily! So here is a simpler version in H2 =INDEX(SINo,MATCH(1,SIGN((COUNTIF($H$1:$H1,SINo)<RepeatAmt)),0)) enter with Ctrl + Shift + Enter For repeating sequences, try the following in cell G2...

Hi Ramanan, Welcome to the forum! If your list could repeat, then try the following array formula in cell G2 and copy down =INDEX(SINo,MATCH(1,SIGN(ISNA(MATCH(SINo,G$1:G1,0))+(COUNTIF($G$1:$G1,SINo)<SUMIF(SINo,SINo,RepeatAmt))),0)) enter with Ctrl + Shift + Enter If your list does not...

Hi, Just for fun, here is one more approach for calculating number of overlapping months: =TEXT((NPV(0,TEXT(MIN(D2,D3),{"y","m"})/{1,12})-NPV(0,TEXT(MAX(C2,C3),{"y","m"})/{1,12}))*12+1,"#;0")+0 Cheers, Sajan.

Hello Rahul, Not sure if you wanted to change the date in cell C2 to a date in the year 2010 or 2013. The counts do not change when the date is in 2010 since that date is less than the date in F23 Cheers, Sajan.

Hi Rahul, I would encourage you to try out a formula before declaring it as non-functional. I know the formula works correctly since I tried it out multiple times. Regarding your questions... A$1 in that formula creates an array (such as {1;2;3;4;5} proportionate to the length of the string...

Hi Rahul, Not sure what to say... I just copied / pasted the above formula from this page onto an Excel worksheet, and it worked for me as expected. (Please note that the formula refers to cell A11 and not A1) -Sajan.

Hello Rahul, Did you remember to enter the formula with Ctrl + Shift + Enter (instead of just Enter key)?

Hi Ozbear, Thanks for the feedback! Happy to help! Keep visiting this forum... and in no time you will be creating better formulas than the one above! That is what I did! Cheers, Sajan

Hello Rahul, Here is one approach... For value in cell A11 =MID(A11,MATCH(2,1/(MID(A11,ROW(OFFSET(A$1,,,LEN(A11))),1)=" "))+1,LEN(A11)) enter with Ctrl + Shift + Enter Cheers, Sajan.

Hello Rediska, Here is one more array formula for a value in A3: =IF(SUM(N(ISNUMBER(FIND(LEFT(TRIM(MID(SUBSTITUTE(A3," ", REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1))-1)*100+1,100)),1),"[]{}(),0123456789"))))>0,"Bad","Good") enter with Ctrl + Shift + Enter (Same...

Hello Rediska, Try the following array formula against a value in A3 =IF(SUM(N(ISNUMBER(FIND(LEFT(MID(A3, ROW(OFFSET(A$1,,,LEN(A3))), MMULT(FIND(" ",{""," "}&A3&" ",ROW(OFFSET(A$1,,,LEN(A3)))),{1;-1})),1),"[]{}(),0123456789"))))>0, "Bad","Good") enter with Ctrl + Shift + Enter I tried it...

So, the following are considered stand-alone... Please confirm. Any number after a space, as long as it is followed by another space or end of string Any number that has a comma prefix or suffix, including "A,234,B" even though "A" and "B" are adjacent to the commas. Is this a valid condition...

Hello Rediska, Can you clarify what you mean by "stand-alone"? From your sample, it appears that () still qualifies as stand-alone, but if the number is preceded by a single letter, it is not. Are there any other conditions that we should be aware of? Please upload a sample file that is...

A well ripened Halloween pumpkin?!! Since it is Halloween season here, this piqued my interest! What does a well-ripened Halloween pumpkin look like? Is it the size, the softness, the juiciness, aroma, color, or the density of the pumpkin flesh (or perhaps all of the above!) that would be...

Hi Bob, Thanks! -Sajan.

Hi Jaine, Thanks for the feedback. Glad to help! AwardCodes and AwardRates are named ranges in your worksheet that I setup to make referencing easier to read. You can just easily substitute them with the actual ranges. The key advantage to named references is that if you need to change the...

Hello Ozbear, Welcome to the forum. First of all, kudos to you for taking the time to explain your requirements. Wish more posters were like you! Attached is a revised worksheet with the following formula applied to columns H to T in the Calculations tab. Let me know if that is what you...

Brevity is the soul of wit! Unfortunately, that does not apply to explanations! Since I still do not know what you are after, I would encourage you to upload a worksheet with at least a mockup of what you are after.

Hi Sachinbizboy, I will look to others to comment on any of the VBA books out there. (I have not read any of them since I find a quick Google search is more efficient for my occasional dabbling in VBA!) I would encourage you to get at least a basic understanding of data structures, data...