Excel whizz kids???

[Guest]

I require some help on excel.  I have a range of tests I need to do, and I'm stuck on two.  I need to evaluate a range of data and get excel to count the numbers of cells when the below appears in a cell:

non alphanumeric characters

non ASCII characters

Any whizz kids out there that can help....please :-)

 

[Gaspode]

Definitely not an expert, but I think you need to write a bit of code as the built in COUNTIF function doesn't include the options you're after - I found this regarding the non-ASCII count which may help: http://www.mrexcel.com/forum/excel-questions/456798-identifying-if-cells-contain-non-ascii-characters.html

[Guest]

Thanks @Gaspode I'll give it a go

[needles]

1 hour ago, Leeds Ram said:

I require some help on excel.  I have a range of tests I need to do, and I'm stuck on two.  I need to evaluate a range of data and get excel to count the numbers of cells when the below appears in a cell:

non alphanumeric characters

non ASCII characters

Any whizz kids out there that can help....please :-)

 

Not at work and haven't got Excel at home, and I'm competent rather than a whizz kid, but I think you'll want "countif". I think you then give the range and logical statement pertaining to the characters you need. Investigate that in Excel and it should be do-able.

[Guest]

27 minutes ago, needles said:

Not at work and haven't got Excel at home, and I'm competent rather than a whizz kid, but I think you'll want "countif". I think you then give the range and logical statement pertaining to the characters you need. Investigate that in Excel and it should be do-able.

Thanks needles, I have around 40 different tests of which the majority work with countif and countifs however these are the only the 2 where I can't find a condition to match on.  

[davenportram]

4 minutes ago, Leeds Ram said:

Thanks needles, I have around 40 different tests of which the majority work with countif and countifs however these are the only the 2 where I can't find a condition to match on.  

try setting up a "check" range that returns 1 for non-alpha numeric and 0 for alpha numeric and then doing a sum of the "check" range.

Not sure how easy it is to set up this check but I often have check columns on tables so I can rank by different criteria.

[eddie]

36 minutes ago, needles said:

Not at work and haven't got Excel at home, and I'm competent rather than a whizz kid, but I think you'll want "countif". I think you then give the range and logical statement pertaining to the characters you need. Investigate that in Excel and it should be do-able.

Alternatively, write a VBA script.

[davenportram]

just found this for the alphanumeric check, with the additon of an IF function then it could be used to return 1 or 0 for non alpha numeric/numeric cells.

https://answers.yahoo.com/question/index?qid=20120113102337AAMpHOd

do the cells only have 1 character in or a string of characters?

[davenportram]

oh what are non ASCII characters

[i-Ram]

2 hours ago, Leeds Ram said:

I require some help on excel.  I have a range of tests I need to do, and I'm stuck on two.  I need to evaluate a range of data and get excel to count the numbers of cells when the below appears in a cell:

non alphanumeric characters

non ASCII characters

Any whizz kids out there that can help....please :-)

It's a shame you aren't a member of the Breakfast Gang. You would have Mel and his IT Support Team on speed dial. And only for the price of your soul.

[Boycie]

1 hour ago, i-Ram said:

It's a shame you aren't a member of the Breakfast Gang. You would have Mel and his IT Support Team on speed dial. And only for the price of your soul.

Don't start him off again.

i thought LeedsRam was a whizz on the old BBC computers at school?

[davenportram]

5 hours ago, Leeds Ram said:

I require some help on excel.  I have a range of tests I need to do, and I'm stuck on two.  I need to evaluate a range of data and get excel to count the numbers of cells when the below appears in a cell:

non alphanumeric characters

non ASCII characters

Any whizz kids out there that can help....please :-)

 

Sorted the check and count for non alpha-numeric characters, and it checks for any non alphanumeric characters in a string of characters within a cell.

 

The green cells are the ones to be checked, the orange cells is a "check" table of sorts that returns 1 is non alphanumeric and 0 is it is alpha numeric. Then there are counts below.

 

Here is the "check formula". If you copy it into the top left cell of £check table then copy it to all others it should work. 

=IF(A1="","",IF(ISERROR(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),"0",1))

[Guest]

11 hours ago, davenportram said:

Sorted the check and count for non alpha-numeric characters, and it checks for any non alphanumeric characters in a string of characters within a cell.

 

The green cells are the ones to be checked, the orange cells is a "check" table of sorts that returns 1 is non alphanumeric and 0 is it is alpha numeric. Then there are counts below.

 

Here is the "check formula". If you copy it into the top left cell of £check table then copy it to all others it should work. 

=IF(A1="","",IF(ISERROR(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),"0",1))

Thanks @davenportram that is much appriacted.  I'll give it a go Monday and let you know how I get on. Once again....cheers

[Guest]

13 hours ago, Boycie said:

Don't start him off again.

i thought LeedsRam was a whizz on the old BBC computers at school?

My one serious post all year and you come to take the piss :-)

As for school, I was never any good at anything, as you should know.

[davenportram]

2 hours ago, Leeds Ram said:

Thanks @davenportram that is much appriacted.  I'll give it a go Monday and let you know how I get on. Once again....cheers

Heres a similar one for the Ascii check too. I had to create reference table for ASCII characters and then a cell that held a string of all ASCII characters to check against, then edit the check formula from the alpha-numeric check.

 

The none ascii character I used to check the funcionality is "Ԁ" which looks like a d but isn't. 

 

Obviously this is just my way of doing it and there may be others. You could use functions embedded in the countif function to do the count without using a check range, but that's for another day.

[froggg]

Kinell that might as well be double dutch to me...

[davenportram]

7 minutes ago, froggg said:

Kinell that might as well be double dutch to me...

at first it looked hard to me. But google helped with solving a similar alpha numeric check problem. A little editing and I solved the problem.

 

not bad for someone completely self taught through brute force, ignorance and of course google.

[Guest]

On 4 June 2016 at 11:49, davenportram said:

Heres a similar one for the Ascii check too. I had to create reference table for ASCII characters and then a cell that held a string of all ASCII characters to check against, then edit the check formula from the alpha-numeric check.

 

The none ascii character I used to check the funcionality is "Ԁ" which looks like a d but isn't. 

 

Obviously this is just my way of doing it and there may be others. You could use functions embedded in the countif function to do the count without using a check range, but that's for another day.

Thanks again Dav, unfortunately using a check range wasn't an option as the files I have range from 250-300k per column, however I was able to take your sheet an incorporate it within some count formulas to give the required result...cheers

[davenportram]

3 minutes ago, Leeds Ram said:

Thanks again Dav, unfortunately using a check range wasn't an option as the files I have range from 250-300k per column, however I was able to take your sheet an incorporate it within some count formulas to give the required result...cheers

excellent. Glad I could help.