Search results

Hi Narayan, Yes, the calculated string would be in column C on "Customer vs Job Hours" sheet. The data would come from Column E of "JZU October 2013" tab. (Column I shows the Job Departments, even though the heading says Job legend.) Regards, Sajan.

Hi Van Gysel, Here is another approach that does not use any helper cells: Put the following array formula in cell E29, and copy down and to the right: =SUM(LOOKUP("N"&IFERROR(1/(1/MMULT(N($E$13:E$17>0),TRANSPOSE(COLUMN($E$13:E$17)^0)))-1,0), $D$20:$M$20, $D21:$M21)*E$13:E$17) enter with...

Hi Narayan, In the previous post, the OP wanted to display the multiple job numbers in a single cell. For example, a single string that says "E2147, E2430" in cell C6. That is why I suggested a VBA approach. -Sajan.

I am not sure what that means in this context.

Hi Shrivallabha, I interpreted the OP's question to mean the following results: abc123-qw One Two --> abc123-qw One 0123213 Two --> 0123213 One Two abc123-qw123 --> abc123-qw123 Regards, Sajan.

Hi Venkata, Nice! You can shorten it slightly as follows: =MID(A1,LEN(A1)-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))+1,LEN(A1)) Give some thought to how you would extract numbers from the following strings also: 1234ABC --> result should be 1234 ABC1234DEF --> result should be...

Hi Venkata, As you discovered, a formula can be greatly simplified to meet a specific need. Now, how would you generalize your formula if the alphabets are not limited to the left-most position? (For example, "ABC12345") -Sajan.

Hi Narayan, You are correct. The formulas can be greatly simplified. (My original intent was to return the results as an array.) Regards, Sajan.

Hello Jordi, For hours in cell A1 and duration in B1, try in cell C1: =IF(COLUMN(A1)>$B1,"---",($B1+1-COLUMN(A1))*($A1/(($B1/2)*(1+$B1)))) Copy to the right until you get "---" Cheers, Sajan.

SirJB7, While it may be traumatic for the therapist, I would encourage you to continue the therapy since it would be good for the economy!! :p -Sajan.

I tried this with IE 8 and Chrome version 31, and did not get any special characters. It could be because I copy and paste as text.

Hi SirJB7, We knew we were normal!! Thanks for confirming the corollary about the Ninjas!! :D -Sajan.

Hi, To get rid of any non-numeric characters from a string, assuming that the resulting extract is a valid Excel number, you can try one of the following approaches: For a string in cell A28...

Hi Xiq, Very interesting... It must be how I copy/paste text from posts... I paste as text, and do not get these special characters. -Sajan.

Hi Xiq, One approach to try is to multiply each digit with the power of 10, starting with the rightmost digit, and skipping anything that is non-numeric. Cheers, Sajan.

Hello, The following article by Colin Legg is a great summary of common pitfalls with VLOOKUP: http://colinlegg.wordpress.com/2012/03/26/why-does-vlookup-return-an-na-error/ Cheers, Sajan.

Hi Dan, Are you looking for a macro solution for the negative sign suffix, or a formula solution? -Sajan.

Hi, Can you check if the following post is helpful? http://chandoo.org/forum/threads/date-of-birth-formula-for-next-birthday-whether-in-2013-or-2014.12721/ Cheers, Sajan

Thanks for the feedback! Glad to help! Welcome back anytime!

Here is one more... slightly shorter: =LEFT($A2,LOOKUP(30,FIND(" ",$A2,ROW(OFFSET(A$1,,,LEN($A2))))))

Hi, For a string in A2, try =LEFT($A2,LOOKUP(30,ROW(OFFSET(A$1,,,LEN($A2)))/(MID(A2,ROW(OFFSET(A$1,,,LEN($A2))),1)=" "))) Cheers, Sajan.

Unless you are asking about looking up something inside a text string. Use FIND or SEARCH for that.

Where are you guys finding the non-breaking space? Since the OP did not post a workbook, I looked in the file that Xiq uploaded, and found only regular spaces in the strings. Your comments reminded me of the movie "Sixth Sense" where the little boy said "I see dead people"... in your case, it...

Hi SirJB7, Did not see your post in time! -Sajan.

On Sheet2, to get the counts in col B, put in cell B2 and copy down =COUNTIF(Sheet1!$A$2:$A$19,Sheet2!$A2) On Sheet2, column D, put in cell D2 and copy down: =SUM(COUNTIF($A2,IF((Sheet1!$A$2:$A$19=$A2)*(Sheet1!$B$2:$B$19<>""),Sheet1!$A$2:$A$19))) enter with Ctrl + Shift + Enter On Sheet2...