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

 

[Kyle123]

Can’t you just use vlookup?

 

[jaaimee]

I went to read up on vlookup. But it is not giving me the results that i prefer. It would work if i have 2 columns of data to begin with. However, in the project I am working on, I am only given column A (acronyms) and I need to input the expansion of the acronyms into column B manually. Vlookup seems to be like a function for searching and returning the value you are looking for.

 

[Fluff]

If you setup a lookup table with the acronym & phrase you can use vlookup like

Excel 2013/2016
	A	B	C	L	M	N
1	Acronym	Phrase			Acronym	Phrase
2	AHU-L8-01	Air Handling Unit			AHU	Air Handling Unit
3	AHU-L9-01	Air Handling Unit			FCU	Fan Coil Unit
4	AHU-L10-01	Air Handling Unit
5	FCU-L8-01	Fan Coil Unit
New

Cell Formulas
Range	Formula
B2	=VLOOKUP(LEFT(A2,3),M$1:N$3,2,0)

 

[BlueHornet]

I'd modify Fluff's formula just a bit - since presumably some acronyms will vary from the 3 characters you now show - to:

=VLOOKUP( LEFT( A6, FIND( "-", A6) - 1), $E$2:$F$100, 2, FALSE)

However, this presumes that ALL of the leading acronyms will be followed by a "-" dash. The formula will have to change if you have differing methods of entering the part numbers.

 

[jaaimee]

Thanks for all the reply. Hmm. Correct me if i am wrong. I tried the formula and i saw the word "Array". This is using the concept of "Array"?

Will it be possible if i do not want the expanded acronyms to appear in the same sheet?

I will be trying it out in VBA as well.

 

[Fluff]

Thanks for all the reply. Hmm. Correct me if i am wrong. I tried the formula and i saw the word "Array". This is using the concept of "Array"?

Not sure what you mean by this, where did you see the word "array"?

The lookup table can go on another sheet & you'd amend the formula like
=VLOOKUP(LEFT(A2,3),sheet1!A$1:B$3,2,0)
assuming it was on sheet1 in cols A:B

 

[BlueHornet]

It's no trick to do the VLookup in a different worksheet, even a different workbook. You just need to properly qualify the reference with the sheet name and (if used) the path and external file name as well. If you've never used the formula before (sometimes even if you have!) then it might be easiest to build the formula with "point and click" references rather than typing it from scratch, but that's not so difficult after you get used to the syntax of how you qualify worksheet and workbook names (and paths, if those vary, too).

 

[jaaimee]

I am so sorry if i confuse you there. I saw it when i hover over the formula and i saw the word "table_array", right after VLOOKUP(lookup_value, .

 

[jaaimee]

It's no trick to do the VLookup in a different worksheet, even a different workbook. You just need to properly qualify the reference with the sheet name and (if used) the path and external file name as well. If you've never used the formula before (sometimes even if you have!) then it might be easiest to build the formula with "point and click" references rather than typing it from scratch, but that's not so difficult after you get used to the syntax of how you qualify worksheet and workbook names (and paths, if those vary, too).

Without any programming background and barely touching Excel in my daily life, all of the codes and formulas look like alien language to me. Plus, this is only one of the functions that i wish to achieve. I am still trying to figure things out.