Excel Macro to expand acronyms

J

jaaimee

New Member
Joined
Nov 8, 2018
Messages
16
Hi everyone,

I am working on a project that requires me to expand the acronym in column A of excel and copy the expanded term to column B. The acronyms i am working on mainly revolves around the engineering mechanical side. An example would be:

Column A Column B
AHU-L8-01 Air Handling Unit
AHU-L9-01 Air Handling Unit
AHU-L10-01 Air Handling Unit

FCU-L8-01 Fan Coil Unit
FCU-L9-01 Fan Coil Unit
FCU-L10-01 Fan Coil Unit

It revolves alot of repetitive work therefore i hope to find a macro that can cut down the time taken on this.
I really hope there is someone who is able to provide some direction as to where should i start as i am a beginner in VBA.

Any help is greatly appreciated! Thank you in advance!

Jaime :)
 
Sort by date Sort by votes
If you create a new sheet called "List" & paste this into it from Cell A1


Excel 2013/2016
AB
1AcronymPhrase
2AHUAir Handling Unit
3FCUFan Coil Unit
4PCUPower Control Unit
5ARUAir Reduction Unit
6ATCAir Temperature Controller
List


You can then use this formula on your data
=VLOOKUP(LEFT(A2,3),List!A$1:B$500,2,0)
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Okay, take a breath. Here's a simple way for anyone to build a formula in Excel using standard functions.

Let's say that you want to do the VLookup that several here have suggested. We'll assume that you have a worksheet containing a table of the values you want to look up and their corresponding meanings. (We'll also assume that the "lookup values" are unique, though the corresponding values need not be.)

Start writing the formula - where you want the result to appear - "=vlookup(" (not the quotation marks). AS SOON AS YOU HAVE THAT - AND ONLY THAT - hit "Ctrl-A" on your keyboard. You'll be presented with a wizard dialog box that will help you to build the formula. All you need to do is use your mouse to click each box of the dialog, and then click off to the various values (or tables) you want to look up in. You'll get the hang of it. (At one time none of us knew any Excel, either.)
 
Upvote 0
Fluff said:
If you create a new sheet called "List" & paste this into it from Cell A1

Excel 2013/2016
AB
1AcronymPhrase
2AHUAir Handling Unit
3FCUFan Coil Unit
4PCUPower Control Unit
5ARUAir Reduction Unit
6ATCAir Temperature Controller

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
List​



You can then use this formula on your data
=VLOOKUP(LEFT(A2,3),List!A$1:B$500,2,0)
Click to expand...

If i wish to turn into a VBA code, is it possible?
 
Upvote 0
BlueHornet said:
Okay, take a breath. Here's a simple way for anyone to build a formula in Excel using standard functions.

Let's say that you want to do the VLookup that several here have suggested. We'll assume that you have a worksheet containing a table of the values you want to look up and their corresponding meanings. (We'll also assume that the "lookup values" are unique, though the corresponding values need not be.)

Start writing the formula - where you want the result to appear - "=vlookup(" (not the quotation marks). AS SOON AS YOU HAVE THAT - AND ONLY THAT - hit "Ctrl-A" on your keyboard. You'll be presented with a wizard dialog box that will help you to build the formula. All you need to do is use your mouse to click each box of the dialog, and then click off to the various values (or tables) you want to look up in. You'll get the hang of it. (At one time none of us knew any Excel, either.)
Click to expand...

I guess i did not understand Excel well enough. Didn't know vlookup existed. :LOL:
 
Upvote 0
If you insist on using code, this might do it.
Assumes your data starts in A1 and Column B is free to receive the result.
Code: 
Sub Maybe()
Dim acrArr, fnArr, c As Range, i As Long
    acrArr = Array("AHU", "FCU")    '<---- expand as required
    fnArr = Array("Air Handling Unit", "Fan Coil Unit")    '<---- expand as required
        For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            For i = LBound(acrArr) To UBound(acrArr)
                If Left(c, Len(acrArr(i))) = acrArr(i) Then c.Offset(, 1) = fnArr(i): Exit For
            Next i
        Next c
End Sub
 
Upvote 0
jolivanes said:
If you insist on using code, this might do it.
Assumes your data starts in A1 and Column B is free to receive the result.
Code: 
Sub Maybe()
Dim acrArr, fnArr, c As Range, i As Long
    acrArr = Array("AHU", "FCU")    '<---- expand as required
    fnArr = Array("Air Handling Unit", "Fan Coil Unit")    '<---- expand as required
        For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            For i = LBound(acrArr) To UBound(acrArr)
                If Left(c, Len(acrArr(i))) = acrArr(i) Then c.Offset(, 1) = fnArr(i): Exit For
            Next i
        Next c
End Sub
Click to expand...

I will give it a try with that! Thank you~

Meanwhile i tried to do up one with some examples i found online, it can't seem to work.

Code: 
Sub Acronyms()On Error Resume Next
Dim AC_Row As Long
Dim AC_Clm As Long
Table1 = Sheet1.Range("E6:E16")
table2 = Sheet3.Range("A2:B6")
AC_Row = Sheet1.Range("A6").Row
AC_Clm = Sheet1.Range("A6").Column


For Each cl In Table1
  Sheet1.Cells(AC_Row, AC_Clm) = Application.WorksheetFunction.VLookup(cl, table2, 2, True)
  AC_Row = AC_Row + 1
Next cl
End Sub

I had error pop-ups when i tried to run the code. Stating "unable to get vlookup property of worksheetfunction class". I am curious to know what's the issue with it?
 
Upvote 0
I had error pop-ups when i tried to run the code. Stating "unable to get vlookup property of worksheetfunction class". I am curious to know what's the issue with it?
Click to expand...
That means it could not find a match. Probably because you are trying to match the entire cell contents, rather than just the first 3 letters.

Are all the acronyms 3 characters long?
 
Upvote 0
If you have this in Sheet2, starting at A1

AHU-L8-01
FCU-L8-01
AHU-L9-01
FCU-L10-01
AHU-L10-01
FCU-L9-01
ATC-M7-165
ARU-K17-6543
PCU-L4-87
FCUS-L9-342


and this in Sheet3 starting at A1/B1

AHU Air Handling Unit
FCU Fan Coil Unit
PCU Power Control Unit
ARU Air Reduction Unit
ATC Air Temperature Controller
FCUS Fan Coil Unit Secondary


This will be the result in Columns A and B in Sheet2

AHU-L8-01 Air Handling Unit
FCU-L8-01 Fan Coil Unit
AHU-L9-01 Air Handling Unit
FCU-L10-01 Fan Coil Unit
AHU-L10-01 Air Handling Unit
FCU-L9-01 Fan Coil Unit
ATC-M7-165 Air Temperature Controller
ARU-K17-6543 Air Reduction Unit
PCU-L4-87 Power Control Unit
FCUS-L9-342 Fan Coil Unit Secondary

with both of these codes.

Code: 
Sub Maybe_A()
Dim acrArr, fnArr, c As Range, i As Long
    acrArr = Array("AHU", "FCU", "PCU", "ATC", "ARU", "FCUS")
    fnArr = Array("Air Handling Unit", "Fan Coil Unit", "Power Control Unit", "Air Temperature Controller", "Air Reduction Unit", "Fan Coil Unit Secondary")
        For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            For i = LBound(acrArr) To UBound(acrArr)
                If Left(c, InStr(c, "-") - 1) = acrArr(i) Then c.Offset(, 1) = fnArr(i): Exit For
            Next i
        Next c
End Sub

Code: 
Sub Maybe_B()
Dim a, c As Range
a = Sheets("Sheet3").Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        c.Offset(, 1) = a(Application.Match(Left(c, InStr(c, "-") - 1), Application.Index(a, , 1), 0), 2)
    Next c
End Sub
 
Upvote 0
jolivanes said:
If you have this in Sheet2, starting at A1

AHU-L8-01
FCU-L8-01
AHU-L9-01
FCU-L10-01
AHU-L10-01
FCU-L9-01
ATC-M7-165
ARU-K17-6543
PCU-L4-87
FCUS-L9-342


and this in Sheet3 starting at A1/B1

AHU Air Handling Unit
FCU Fan Coil Unit
PCU Power Control Unit
ARU Air Reduction Unit
ATC Air Temperature Controller
FCUS Fan Coil Unit Secondary


This will be the result in Columns A and B in Sheet2

AHU-L8-01 Air Handling Unit
FCU-L8-01 Fan Coil Unit
AHU-L9-01 Air Handling Unit
FCU-L10-01 Fan Coil Unit
AHU-L10-01 Air Handling Unit
FCU-L9-01 Fan Coil Unit
ATC-M7-165 Air Temperature Controller
ARU-K17-6543 Air Reduction Unit
PCU-L4-87 Power Control Unit
FCUS-L9-342 Fan Coil Unit Secondary

with both of these codes.

Code: 
Sub Maybe_A()
Dim acrArr, fnArr, c As Range, i As Long
    acrArr = Array("AHU", "FCU", "PCU", "ATC", "ARU", "FCUS")
    fnArr = Array("Air Handling Unit", "Fan Coil Unit", "Power Control Unit", "Air Temperature Controller", "Air Reduction Unit", "Fan Coil Unit Secondary")
        For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            For i = LBound(acrArr) To UBound(acrArr)
                If Left(c, InStr(c, "-") - 1) = acrArr(i) Then c.Offset(, 1) = fnArr(i): Exit For
            Next i
        Next c
End Sub

Code: 
Sub Maybe_B()
Dim a, c As Range
a = Sheets("Sheet3").Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        c.Offset(, 1) = a(Application.Match(Left(c, InStr(c, "-") - 1), Application.Index(a, , 1), 0), 2)
    Next c
End Sub
Click to expand...

For the code above, I realise the LEN function was changed to Instr. LEN function is used to calculate the length of the cell, correct?
I can sort of understand why Instr is used since the characters from the list of acronyms i have is not standard, it may varies. The only "constant" inside is the dash.

However, I cannot really understand the second code. Sorry~
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Automatically import select number of rows and columns based on user-input from .txt file using VBA
Replies
5
Views
1K
Micron
M
L
  • Question
Excel VBA - Return Specific Data from Master Sheet Based on Date Search + Duplicate Certain Items
Replies
1
Views
480
LookingForHelp1
L
J
  • Solved
VBA macro to copy rows/columns based on cell value
Replies
5
Views
672
Josu
J
E
  • Solved
[VBA] File saving does not work/new version not uploaded
Replies
4
Views
522
NdNoviceHlp
NdNoviceHlp
E
  • Solved
Macro to insert helper column and formula
Replies
9
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,102
Messages
6,128,851
Members
449,471
Latest member
lachbee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top