Excel Macro to expand acronyms

[jaaimee]

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

 

[Fluff]

If you create a new sheet called "List" & paste this into it from Cell A1

Excel 2013/2016
	A	B
1	Acronym	Phrase
2	AHU	Air Handling Unit
3	FCU	Fan Coil Unit
4	PCU	Power Control Unit
5	ARU	Air Reduction Unit
6	ATC	Air Temperature Controller
List

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

 

[BlueHornet]

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.)

 

[jaaimee]

If you create a new sheet called "List" & paste this into it from Cell A1

Excel 2013/2016

	A	B
Acronym	Phrase
AHU	Air Handling Unit
FCU	Fan Coil Unit
PCU	Power Control Unit
ARU	Air Reduction Unit
ATC	Air Temperature Controller

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

</tbody>

List​

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

If i wish to turn into a VBA code, is it possible?

 

[jaaimee]

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.)

I guess i did not understand Excel well enough. Didn't know vlookup existed.

 

[jolivanes]

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.

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

 

[jaaimee]

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.

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

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.

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?

 

[Fluff]

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?

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?

 

[jaaimee]

Nope. It ranges from 2 to 5 characters, sometimes even more with the addition of brackets.

 

[jolivanes]

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.

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

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

 

[jaaimee]

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.

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

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

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~