Determine latin characters in cells

Hi akotronis,

I guess you can use Search function here.. can you upload a sample workbook? thanks.

Regards,
DILIPandey
<click on below * if this helps>

Καλησπέρα.

My suggestion only gives a zero if enen 1 latin character exists.

Type in a coumn the English alphabet. Name it as List.

Then in A1 and copy down use this.

=IFERROR(LOOKUP(2^15;SEARCH(List;A1);A1);"")

One thing that I didn't know before this thread is that CODE applied to any Greek character returns 63, so you can make use of this to count how many characters give a code outside this value (the space character returns 32, and you have one of these in each name). Put this array* formula in cell D1 (for example):

=IF(SUM(IF(CODE(MID(A1,ROW(INDIRECT("$1:"&LEN(A1))),1))<>63,1))>=2,"Too many Latins","")

then copy it down. You might want to adjust the >=2 if you have more than a single space. If you just use this array* formula:

=SUM(IF(CODE(MID(A1,ROW(INDIRECT("$1:"&LEN(A1))),1))<>63,1))

then it will count how many latin characters you have in each cell.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

EDIT: Sorry, forgot to attach the sample file:

...you have to find the unicode/hex number for the font style you are querying.

Perhaps that why Pete's formula looks to don't work for us. Or Local settings...I am quite sure that we see something else than you see in UK.

I have a picture in my sheet of what we see.

..2) @Fotis1991, thank you. I am not sure though I understand how the formula works. Specifically the use of 2^15 and the reason it returns the zero when it does.

Formula looks to match any letter of the list to eatch cell in column A.

As the maximum number of characters in a cell is 2^15 -1 (32,767) then 2^15 will fulfil that requirement

Yes Pete exactly as i suspected..

Perhaps that why Pete's formula looks to don't work for us

I see what you see in the picture.

My poor English don't let me to understand well, what Marcol means..

We see completely different things..

@ Fotis
Could you open the attached file and, with the yellow cells, copy > paste special > values?

If you could then post the result we might better know what a Greek version of Excel sees.

Part of the problem so far is if the strings contain spaces, commas, full stops, etc, or even numbers they will be read as "Latin", that is these universal characters will not return CHAR(63).

I have some UDFs for returning Hex/Unicode. They might be adaptable to get a more universal solution to this thread.

[EDIT]
Could you add to this, in say P1, drag down to P255.

Formula:

Please Login or Register  to view this content.

Again Paste Special > Values, the result you get.

Thanks

Here you are Marcol.

And a new one inclunding char...

Hmm?
That isn't at all what I expected to see!

Could we please try again with this workbook?

Just copy > Paste Special > Values , the yellow cells only. Paste the result over the original formulae.

There is more to this than I thought there might be, I expected the result might show some greek characters returned in Column P at least.

Here you are.......

@ Fotis

See what you get with this workbook before I go down the Unicode route.

Apologize for the delay to reply my friend. Today is Saturday...as you know

YES. Now we see the same result! Excellent work!

When and if you find some free time, would you pls explain your Original formula and formulas for Names(LowerCodes--UpperCodes)??

Named Ranges are always treated as arrays by Excel

Formula:

Please Login or Register  to view this content.

So COLUMN(Sheet1!$A:$Z) is effectively {1,2,3......23,24,25,26}
By adding 64 we get {65,66,67......87,88,89,90} the Ascii codes for A-Z.

Similarly by adding 96 we get the codes for a-z.

Notice that we use COLUMN() not COLUMNS()
COLUMNS() returns a single value, in this case it would be 26.

Be careful where you use this, if any columns in the range A:Z are deleted the named range will change.
To minimize the risk of this happening we might refer to a higher range of columns
e.g.

Formula:

Please Login or Register  to view this content.

Equally we might use

Formula:

Please Login or Register  to view this content.

See if this workbook helps.