Greek character substitution in Excell spreadsheets / CSV files

Posted on 2006-06-08
Last Modified: 2012-06-21
I have a  bunch of comma separated files (CSV), extracted from EXCEL  spreadsheets,  which contain the lowercase Greek character “theta”. I need to change that character to “t”, so that other non-MS programs can process those CSV files. Assume that all files starting with AA and BB in a given directory Z:\WORK   need to be processed.

What is the VB program/macro that can do this job?

Question by:eruskov
    LVL 18

    Expert Comment


    Assuming that the only non-English character is the lower case theta, the following macro will turn all theta to lower "t" in a spreasheet:
    Sub MacroTheta()
    ' MacroTheta Macro
        Cells.Select    ' this selects the entire page
        Selection.Replace What:="?", Replacement:="t", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
    End Sub

    what you need is to open the files "AA*.csv", run MacroTheta and save "AA*.csv".
    Are the files organized as AA01.csv, AA02.csv, etc.? If this is the case, you can read and save the modified files with AA01modified.csv names, by using another macro that reads, calls MacroTheta and saves the files.

    If the names can't be called as described above, you can generate a list at the prompt with:
    dir AA*.csv /b >list.txt

    then open list.txt in excel (it will read into a collumn), create a new collumn with new filenames for the modified files and read first collumn line by line (with a for statement) as names for files to be open, run MacroTheta, and saves by using the names in the second collumn.

    Simple, but runs.


    Author Comment

    This macro changed all worksheet characters to t !!!
    LVL 15

    Accepted Solution

    Try using the unix-type command tr, which can be used to change the characters in the cvs file, something like:

    tr "\xxx" "t" <ExcelOutput.csv >updatedOutput.csv

    where "xxx" is the octal value of the theta character, which varies depending on the encoding you're using.

    Author Comment

    Thanks, that worked!

    CSV fils are ASCII, so it was possible to massage them on a UNIX computer.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    INTRODUCTION We all know how to code. But at times you simply want to insert a common code block into your existing code and amend it as per your requirements. This tool not only saves you time but also saves you the pain of typing it all out aga…
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now