How do I abbreviate words?

[shangud]

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

 

[Pete_UK]

Do you only have one entry in a cell that needs to be abbreviated, or
might you have several words/phrases in a cell that need to be
changed? If the latter, what is the maximum number of words that would
need to be changed? Presumably, if you have any words that are not in
your dictionary then these would remain unchanged?

Depending on your answers to the above, you could think about using
the REPLACE function or the SUBSTITUTE function or even VLOOKUP in a
formula which can be copied down a helper column.

Hope this helps.

Pete

 

[JLatham]

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.