Hi,
For understanding purpose, lets assume we have B2 instead of B2:B13, fine.
The Text function formats the value (first parameter) according to the
format (the second parameter). So actually the first time we use text()
function, it actually tests if the cell (B2) has the value that matches the
format "SomeText/3digits/2digits". At least that is what it is supposed to
do. But actually it checks if the cell has alphanumeric. It really does not
check for "/3digits/2digits". At the same time, it checks for only numeric
(evaluates to False, if only numeric). The second Text() function sees if
the cell(B2) contains (/ - front slash). It just checks for one occurance,
and not two. So combining the two text() function, take us some-what closer
to our requirement, of alphanumeric ("Text/3digs/2digs"). Conclusion is,
this is just an adamant approach to get what we are looking for, and not
full-proof and this formula can be easily fooled. Adamant because, we are
trying to puff the entire logic in just one formula, and there is a better
way (if you want to use VBA) to accomplish pattern-matching, with RegExp -
the Regular Expression Object. And if you still want to use, only formulas,
then you will need to use some temporary columns, to evaluate inter-mediate
multiple formulas.
Now coming to B2:B13 and the CTRL+SHIFT+ENTER (array formula funda). It just
evaluates the formula taking a range (array of values). The fist If(),
construct an array of True Or False (based on the result of first Text()),
the second IF() constructs another array of True/False based on the result
of second TEXT() function. Finally Both the arrays are ANDed, and the
resulting array summed up. All TRUEs are considered 1, and FALSEs as 0.
Hope this satisfies you...
Regards
Ajit.
On Mon, Oct 20, 2008 at 12:46 AM, Mayank Patel <[EMAIL PROTECTED]> wrote:
> HI Ajit.
> I sincerely appreciate your solutions and your contribution to this group.
>
> looking at your solution I want to know what does this formula means and
> how it works
> =SUM(IF(TEXT(B2:B13,"[EMAIL PROTECTED]/000\/00*
> ")=B2:B13,IF(ISERR(SEARCH("/",TEXT(B2:B13,"[EMAIL
> PROTECTED]/000\/00*")))=FALSE,D2:D13
> )))
>
> Rgds
> Mayank
> On Sat, Oct 18, 2008 at 7:17 PM, AJIT NAVRE <[EMAIL PROTECTED]> wrote:
>
>> Pls find attached the file....
>>
>> Regards
>>
>> Ajit
>>
>> On Sat, Oct 18, 2008 at 2:08 PM, Ahmedhonest <[EMAIL PROTECTED]>wrote:
>>
>>>
>>> Dear All,
>>>
>>> A warm Greetings to all. Please find the attach file to this mail and
>>> request you all Guys to provide me the solution and have patients in reading
>>> the file throughly.
>>>
>>> Hoping that there is a Prompt reply and I Thanks each and every
>>> individual in advance for all your suggestions.
>>>
>>> Regards
>>> --
>>> Ahmed Bawazir
>>>
>>>
>>>
>>>
>>
>>
>> --
>> Thank You,
>>
>> Ajit Navre
>>
>>
>
>
> --
> Regards
> Mayank Patel
> 9822978041
> 9422749110
>
> >
>
--
Thank You,
Ajit Navre
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
