Welcome to the forum!
Have you tried using LEFT(A1,4) in your formulas? If you could attach a cut-down sample workbook showing your reuirements, that would help.
Great forum, learned a lot of things about excel I didn't know before
My problem is this, I have to group some data from a pdf format.
I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...). Can somebody help me? I would really be grateful, cause till now I (and the others before me) used to just print the data and write them down in excel... I really think there can be a lot of easier ways than that :/
thanx in advance
hello! thanx for the very quick reply
I just tried the formula with LEFT, but didn't seem to work... I've attached an example of what I am talking about, and in that mess I have to select data with more than one vague criteria, i.e. sum all the cells that are in a row with the cell with a "FINVALTR" in it, but also with a "Visa", and so on. The problem is that there is only one cell with the criteria and description, and when I try and use 'text to columns', it doesnt' help much, cause not always the text has spaces between words...
Does this od what you are after?
www.imits.co.uk - for all your training and development needs!
use this formula
To Enter the array formula hold down Ctrl and Shift while pushing Enter.
it gives me a value of 28790
Last edited by Dave Hawley; December 13th, 2006 at 19:34.
hi again and thx again
I tried your formula pangolin and it worked perfectly, but when I tried changing the criteria (i.e. instead of "Visa" put "Buxheti" or "Fonde" and instead of "FINVALTR" put "CDISALTR", the formula doesn't work anymore...
how come in some cases it works and in some not? Maybe there is something I don't know about it...
P.S. I did use the ctrl+alt+enter...
try putting everything in capitals
for eg "Fonde" should be put as FONDE...see the UPPER function in the formula which converts the string into upper case and VISA and FINVALTR are in caps
that was done because some words are in upper and some in lower
also if an array formula is modified then the "curly" brackets disappear and the formula needs to be again confirmed by Ctrl+Shift+Enter
There are currently 1 users browsing this thread. (0 members and 1 guests)