• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find and Count words in the list with CYRILLIC NAMES!!!

K

Kanagat

New Member
Hi guys/ladies,


I have question, well i have huge list with pathes listed in worksheet, looks like that


2- Infrastructire & ServicesCAdmin & CControlContractsdocumentation2002-0674_Schedule A.pdf

I need to find and count how many file names contains CYRILLIC characters in their structure.

Thank you,


Best Regards,

Kanagat
 
Hi, Kanagat!


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Hi SirJB7,


Thank you for advice, in my case its huge file with some private information.

It looks like that

File Name Extension Size

1-Additional.pdf pdf 10582.0

2-Additional 1.pdf pdf 205822.0

3-Крутой файл #275.pdf pdf 87547.0

4-Additional asset #3146. pdf pdf 167926.0

5-Additional asset #353.pdf pdf 417834.0


Found:

total file list

5

With Cyrillic

1

File with cyrillic

Крутой файл #275.pdf


The way how it should look like )))

Guys/ladies need your help please


Thank you in advance

Best regards,

Kanagat
 
Hi Kanagat ,


Any Cyrillic character returns a value of 63 when you use the CODE function ; you can make use of this to get what you want.


Suppose we assume that the minimum file length will be 5 or more , then the following formula will tell you whether the file has the first five characters as Cyrillic :


=IF(SUMPRODUCT(--(CODE(MID(A3,ROW(INDIRECT("1:5")),1))=63))=5,"Cyrillic","Non-Cyrillic")


If you use this formula in the column next to your path-names , then getting the count , and getting the filename is a simple matter.


Narayan
 
@NARAYANK991

Hi!

I tested your formula with Kanagat's example and it doesn't work as first two chars (at least) of filename are latin chars too -if "3" and "-" exists in Cyrillic, which I didn't checked.

Changing the INDIRECT parameter for row to "3:7" it worked for the example given.

But I was wondering about the filename structure... maybe that approach it's not valid for the whole file. Let ask Kanagat how does the filename structure looks like.

Regards!


Hi, Kanagat!

Regarding NARAYANK991's formula testing 5 chars starting from the 3rd one, would you please confirm, describe or tell us about the possible positions for the Cyrillic chars within the filenames? Thank you.

Regards!
 
@NARAYANK991


Hi!


Elaborating it a bit more, why not testing if count of Char(63) just greater than zero? As the input are filenames they can't contain the actual Char(63) ("?") question mark sign -unquoted-?


So the formula would be like this:

=IF(SUMPRODUCT(--(CODE(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))=63))>0,"Cyrillic","Non-Cyrillic")

avoiding all constraints regarding filename's structure and things alike.


Regards!


PS: BTW, this will trigger "Cyrillic" label for all non-latin filenames... I guess there are not such cases... but being at Kazajstan, I don't know...


Hi, Kanagat!

Would you please check this correction to the original formula? Thank you.

Regards!
 
Hi, Kanagat!

Glad you solved it, but credit goes to NARAYANK991, I just tweaked it a little. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: Don't forget taking care on non-Latin and non-Cyrillic names, if any.
 
Hi @NARAYANK991,


Thank you for the solution ))). Its nice to have Forum like that!

Again thanks @NARAYANK991


Best Regards,

Kanagat
 
You must log in or register to reply here.
Back
Top