0

I have multiple lists of items in Excel that are in an abbreviated form and I want to set up a macro that will automatically go through the list and replace their abbreviated form with a regular name that also includes characters so I can just run a delineated Text to Columns function in Excel that will allow me to view and sort them properly. For example:

It1
It2
It3
It4

to

Red*Category 1*Item One
Red*Category 2*Item Two
Blue*Category 1*Item Three
Green*Category 2*Item Four

All I need is a simple find and replace for each individual item and I know that I will have to create that from scratch, but each list will have the same items/categories so I don't know the best way to go about it. I am wondering if I should make one long macro in Excel listing each individual find/replace or if it's possible to do something like create an XML file with a

    <find>It1</find>
    <replace>Red*Category 1*Item One</replace>

and just have a macro that references it? My goal is to build one file then have a macro I can use on each list.

pnuts
  • 58,317
  • 11
  • 87
  • 139
D_Zab
  • 710
  • 1
  • 5
  • 14
  • Those are categories I'm going to have to sort by manually. I know I am going to have to go through this whole list and manually categorize things the first time, I want to know the best way to build a find/replace function so I only have to do it once and then I can run that for the other lists and when this comes up in the future. – D_Zab Dec 12 '13 at 16:43

1 Answers1

0

For the moment assuming colours are determined by font, and and using the approach here then with a lookup table as shown the results for the sample data can be achieved with the following formula (copied down to suit): 

=VLOOKUP(B1,$K$1:$M$23,2,0)&"*Category "&RIGHT(A1,1)&"*Item "&VLOOKUP(VALUE(RIGHT(A1,1)),$K$1:$M$23,3,0)

SO20548953 example

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139