CrazyEngineers Archive
Old, but evergreen and popular discussions on CrazyEngineers, presented to you in read-only mode.
@Kaustubh Katdare • 27 Jun, 2016
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):
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 FunctionI've absolutely no experience with Macros and vB scripting. Would appreciate if anyone can help.
@Aashish Joshi • 02 Jul, 2016 • 1 like
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:
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 • 02 Jul, 2016
Python! Thanks a lot, @Aashish Joshi . 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 • 03 Jul, 2016
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. 😁Kaustubh KatdareI'm thinking of learning regex; to make life easier. 😀
3.5k views
Related Posts
@Dhananjay Harkare · Mar 6, 2015
We have already heard about companies like Tesla, Google, Apple and others working on self-driving cars, but now reports claims that Mahindra is going to join this elite league. Indian...
3.7k views
@Ankita Katdare · Dec 19, 2013
The Indian Space Research Organisation said that with the rise of fake Facebook and Twitter profiles about ISRO, the chances of spread of unauthentic information increase. On Monday, it warned...
3.7k views
@ms_cs · Aug 23, 2012
CEans,
I'm a BE graduate and working in MNC as developer. Now I have got 2 years of work experience, I would like to apply for executive mba, or part...
5.5k views
@Abhishek Rawal · Oct 3, 2013
'Smart launcher' is very attractive launcher for Android phones which will give you Ubuntu touch's Unity experience. Smart launcher is consuming very low amount of resources (around 7-8 MB for...
4k views
@Ankita Katdare · Aug 1, 2015
This discussion is dedicated to all civil engineering graduates looking for different career options/ work opportunities after completing B.Tech or B.E. This topic is inspired by a similar thread we...
103.3k views