Substitute parts of Entries by criteria list

[Raph81]

Hello everybody!

I have a problem which I couldn't solve so far. I used the search function and google already but had no success.

Here the problem:

I have a list of filenames imported to Excel. The have the format "AMLF_716.jpg" for example.

Now I have another list with two coloumns that identify the three digits code (in coloumn 1(here f.e. 716)) with a name (in coloumn 2(here f.e. Blue).

As an output I need the new name AMLF~Blue.

I would get it done with substitute if I just have a single value to replace, but with a whole list of criteria it exceeds my Excel skills.

I hope one of you guys has an idea.

I really apprechiate your help!

Thanks,

Raph

[Leith Ross]

Hello Ralph81,

Welcome to the Forum!

I have a few questions:

1. What is the name of worksheet and which column are the file names in?
   

   I have a list of filenames imported to Excel. The have the format "AMLF_716.jpg" for example.

2. What is the name of the worksheet that has the criteria?
3. Where does the output to go?
4. Do you have a sample workbook you can post?

[Raph81]

Hey Leith and thanks for helping and welcoming me

I attached the spreadsheet.

The whole thing is to convert filenames that of pictures that we have to put on a website and they have to have the format

style~colorname

The output can be just in another coloumn in the spreadsheet. This is not really important for me. It's gonna be copy/pasted anyways.

THX,

Raph

[Raph81]

Ah, I just saw that I already replaced the _ by ~ to focus only on the 3 digits.

And some more:

when you look at the spreadsheet you can see that in this specific spreadsheet I already replaced some colorcodes by the names. But this is not important because I have the original data.

On top of it there are colorcodes in the filenames that are not findable in the criterias.

Sry for doublepost

[Leith Ross]

Hello Raph81,

This macro has been added to the attached workbook. All file names that have a matching color code are placed in column "B" of the "Filenames" worksheet.

Please Login or Register  to view this content.

[Raph81]

Thank you so much Leith,

Excel can definitely be magic...

I have some moer questions, because I don't just want to take your work, I want to learn.

1) If I replace the criteria list by another one, does it still work? I mean, same place but different length?

2) Is it possible to get an output where the color code is replaced by the name and if the code isn't findable in the list the output is the original?

3)What are the sorting criterias for the output column?

THX,

Raph

[Leith Ross]

Hello Raph,

Q (1) If I replace the criteria list by another one, does it still work? I mean, same place but different length?

A (1) If the criteria remain in columns "A" and "B" on worksheet "Colorcodes" then nothing needs to be changed. You can keep adding on to the list.

Q (2) Is it possible to get an output where the color code is replaced by the name and if the code isn't findable in the list the output is the original?

A (2) Column "B" of the "Filenames" contains the converted file names. That is the color code number is replaced by the color code description. Column "A" contains the original file names.

Q (3) What are the sorting criteria for the output column?

A (3) I don't understand this question.

[Raph81]

Hey Leith,

to Q 2:

My emphasis was more on the fact if it's possible to get a value back in case the code is NOT in the criteria list in colorcodes and this value should be the original filename.

to Q 3:

forget about it...

[Leith Ross]

Hello Raph,

Are you asking if it is possible to create 2 columns: 1 files that have a matching color code and 1 for those that don't? It is possible to separate the two types.

[Leith Ross]

Hello Raph,

I modified the macro to replace only cells that have matching color codes in column "A". If the there is no color code then file name is unchanged.

Please Login or Register  to view this content.

[Raph81]

Hey Leith,

my workday was over yesterday so I couldn't aswer you directly.

Thanks for the 1a makro. I have really no idea about the code for the makro, but it's very interesting for me. Do you have a good source for information to get started in that field?

I will compare the two codes to see the differences as soon as I have some time. Quite busy at the moment.

You helped me a lot thank you very much!!!

Raph