Macro to convert a formula returned text value into an abbreviated text value

[Rich_B]

Hi

I'm not sure if this is possible but if it is I would appreciate some help with it.

I have a column of data in a table returning text values via a formula. They are typically quite long and I would like to shorten them down.

For example:

One of the returned values might be "Wet-Cast (YELLOW)" I would like to shorten this down to "WC".

The entire list of possible values and the shorter versions I would like instead are:

- "Wet-Cast (YELLOW)" convert to "WC"
- "Wet-Cast (WHITE)" convert to "WC"
- "Wet-Cast (RED)" convert to "WC"
- "Semi-Dry" convert to "SD"
- "GRP (WHITE)" convert to "GRP"
- "GRP (RAL)" convert to "GRP"

The sheet is called "FACTORY_PRINTOUTS" and the range I would need the macro to be active in is D11:D40

Thank you

Excel 2007
Win 8.1
PC

 

[JoeMo]

Why not use a lookup table directly in your formula?

=VLOOKUP(Your formula,lookup range for your table,2,0)

 

[Rich_B]

Why not use a lookup table directly in your formula?

=VLOOKUP(Your formula,lookup range for your table,2,0)

Thanks for your input JoeMo but if I understand you correctly I don't think it will work as nowhere else in the spreadsheet are the shortened values found. They are at full length, this will be the first time they have been abbreviated.

 

[JoeMo]

Thanks for your input JoeMo but if I understand you correctly I don't think it will work as nowhere else in the spreadsheet are the shortened values found. They are at full length, this will be the first time they have been abbreviated.

I'm suggesting you create a lookup table somewhere in your workbook that will list in some column the full values and in the adjacent cells of the next column the desired abbreviations.

 

[Rich_B]

I'm suggesting you create a lookup table somewhere in your workbook that will list in some column the full values and in the adjacent cells of the next column the desired abbreviations.

Hi JoeMo

Thank you for getting back to me to clarify what you meant. It worked perfectly.

Have a great day.