[ExcelVBA] Re: Abbreviations to Words

 

i have used the following: INDEX($P$2:$P$10,MATCH(B2,$O$2:$O$10,0))
where B2 is the first in a column of abbreviations that you want to convert and O2 to O10 is the list in a table of all the abbreviations and P2 to P10 is the list in the table of what you want to convert to.

So table might look like the following with Abbrev and Convert to as titles in row 1

Abrev. Convert to
A watermelon
Z4 orange
M8U apples

etc

--- In ExcelVBA@yahoogroups.com, Steve Tindle <stindle@...> wrote:
>
> Mickey,
>
> Without knowing more about your database it's hard to envision how this would work, but I would investigate adding a new column and add a v-lookup formula in each of the 100,000 rows. When you put in M8U, say in column B, column C would vLookup and convert to Apples. If you didn't want to see the abbreviations then hide column B.
>
>
> This method would save time in programming and you wouldn't have to run a macro every time an abbreviation is changed.
>
>
> Steve
>
>
>
> ________________________________
> From: mickey11030 <mickey11030@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Sunday, September 23, 2012 11:02 AM
> Subject: [ExcelVBA] Abbreviations to Words
>
>
>  
> I have a spreadsheet with about 100,000 rows. One column contains abbreviations that I need to convert to words. Sample: A stands for watermelon, Z4 stands for orange, M8U stands for apples, etc. The abbreviations contain both letters and digits, and are between 1 and 4 characters long. There are about 30 abbreviations in all.
>
> I know I can use a For loop to examine each cell and a Select Case statement to convert the abbreviation in a cell to a word, but I wonder if there is a tricky technique that would do the conversion more efficiently.
>
> Thanks,
> Michael Trombetta
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 2

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___