Excel Formula To Create Abbreviations Using First Letters Of Text

I'm looking for an excel formula that will help me create abbreviations using the first letters of the text in columns -

TEXT ------------------------- ---+------ABBREVIATION
Stanford School of Engineering | SSE
Wollongong University Dubai | WUD

and so on.

Note that the abbreviation excluded the 'of' while creating abbreviation. I'm aware that this can be done by using macros; and have already tried the following code which doesn't work (I keep getting error):

Function GetInitials(ByVal s As String) As String
Dim sExclude As String

sExclude = "the|for|a|an|of" ' words to exclude

With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True

    .Pattern = "\b(" & sExclude & ")\b"
    s = .Replace(s, "")

    .Pattern = "\s*([a-z])[a-z]+\s*"
    GetInitials = UCase(.Replace(s, "$1"))

End With
End Function
I've absolutely no experience with Macros and vB scripting. Would appreciate if anyone can help.

Replies

  • Aashish Joshi
    Aashish Joshi
    Not sure about Excel but it is fairly easy to do this with Perl. 😀

    All you need to do is copy the "full form" column to Notepad and save it as a csv. Give the file's path to perl and let it run its magic 😀

    the drawback with this script is if any letter that needs to be ignored is capital it'll add that to the final abbreviation. Like Indian Institute Of Technology will become IIOT. However, with another condition in the if statement it should be possible to avoid this as well.

    I've attached 2 sample files that I tested the script with. Once you have the final abbreviated.csv just open it in Excel and copy/paste the contents.

    Here's a sample script:
    #!/usr/bin/perl
    use strict;
    use warnings;
    use Text::CSV;
    
    #name of the file where the full forms are kept
    my $fullform = '/path/to/full_name.csv';
    open my $read,"<",$fullform or die $!;
    
    #name of the file where the abbreviations need to be added
    my $abbreviation = '/path/to/abbreviated.csv';
    open my $write,">",$abbreviation or die $!;
    
    # if the first line in the csv is column name
    # uncomment the line below.
    #
    #my $junk = <$fh>;
    
    my $csv = Text::CSV->new();
    
    while(my $line = $csv->getline($read)){
       
        my $abb;
        my @split = split(" ",$line->[0]);
       
        my $len = $#split;
       
        for (0..$len){
            my $char = substr($split[$_],0,1);
            # if the first character is capital process it
            # otherwise do nothing
            if($char =~ m/[A-Z]/){
               
                # in the first run $abb is not
                # defined so make it = $char
                # from 2nd run onwards concatenate it.
                if(!defined $abb){
                    $abb = $char;
                }else{
                    $abb = $abb.$char;
                }
            }
        }
        print $write "\"$line->[0]\",\"$abb\"\n";
    }
    
    close $read;
    close $write;
  • Kaustubh Katdare
    Kaustubh Katdare
    Python! Thanks a lot, #-Link-Snipped-# . This would definitely be handy. Btw, the macro I posted in my first post actually worked fine. It omits the dots (periods) but otherwise, it works just fine. I'm thinking of learning regex; to make life easier. 😀
  • Aashish Joshi
    Aashish Joshi
    Kaustubh Katdare
    I'm thinking of learning regex; to make life easier. 😀
    Oh yes, it makes things soo much easier. I've barely started learning regex and I can already see how easily it gets me out of sticky situations. 😁

You are reading an archived discussion.

Related Posts

Intex is on a roll with yet another budget smartphone - this time the Intex Aqua Classic priced at Rs 4444! The phone comes with a dual GSM SIM card...
Born and brought up in Mumbai and coming from humble beginnings, Kapil Hetamsaria completed his B.E. in Mechanical Engineering from VJTI, Mumbai in 1999. In 2005, he completed MBA in...
The newest entrant in the LYF brand of smartphones from Reliance Industries is the LYF Earth 2. The smartphone in question costs Rs. 20,999 and comes loaded with more smart...
Humans are social animals. We know it, we raise our voice in support of this ideal truth, but due to social responsibilities and work load we fail to prove the...
Educational video games are now becoming the most acceptable form of an emerging alternative to primary education and para-learning methodologies. To Cornell Computer Scientists, the native “Crystallize”, a language learning...