You could start with a formula like this (assuming the long names are in
column A, and we are in row 17 at the moment)

=LEFT(A17,3) & "_" & MID(A17,FIND(" ",A17)+1,3)

The "rules" for that are that there are at least 2 words in the long name
and they are separated by a single space.

But I'd say in a long list, there is a very likely opportunity to replicate
any given abbreviation. You may want an additional helper column to indicate
when a duplicate abbreviation has been created. Again assuming that your
long list starts on row 17 and that the formula I showed earlier is in column
B, then in column C you could put this formula into C17 and fill it down to
indicate when duplicate abbreviations have been created:
=IF(COUNTIF(B$17:B17,B17)1,"DUPLICATED","")
That will display the word "DUPLICATED" anytime the abbreviation on that row
has already been used in a lower numbered row above it.

If you want to turn your formulas that created the abbreviations in column B
into "hard" values after you've developed them and cleaned them up to remove
duplicates, you can select the entire list and use
Edit | Copy
followed by (without unselecting the list)
Edit | Paste Special and making sure the "Values" option is selected. That
will replace the formulas with the results in column B.

"shangud" wrote:

I am a data modeller and I need to abbreviate business names into column
names. I have multiple words in a cell that I have to abbreviate by looking
against a dictionary

For example I have Invoice Amount and it needs to be abbreviated to inv_amt.
I have a worksheet containing 2 columns one with the abbreviation and other
with the complete word.

How do I write a macro to convert a long list of business names in to
abbreviated column names? Any advice is appreciated

Regards