Excel // If Cell-A contains X, return Y in Cell-B

[dibbers006]

The initial message was deleted from this topic on 12 December 2023 at 22:22

[paul.deitch]

You either need a lookup list which could be a lot of work or use vba.

[Doofus]

Are the filenames all the same length?

=if(mid(a1,7,3)="LOL","Laughs Out Loud","")

Or

Put your list of abbreviatons in column D and the translations in column E

=vlookup(mid(a1,7,3),(D1:E20),2,0)

Edited by Doofus on Tuesday 3rd September 12:30

[Neptune188]

dibbers006 said:

I have column of filenames with codes :

12345-ABC-filename
12346-DBE-filename
12347-GGH-filename

I need to reference the bold/italic section and then return a full value in an alternate column.

So Cell-A might read :

12347-LOL-filename

... and Cell-B would then return :

Laughs Out Loud

I have a list of codes, and a list of fullnames but I don't know how to tie the two together.

Anybody au fait with a Formula? As usual with Excel, I'm struggling!

Are the numbers at the front and the lookup always the same length?

If they are:

Create a quick table (we'll call this one Table1):

Syntax | Lookup
LOL | Laugh Out Loud

etc

Then where you want the Lookup to be:

=index(table1[lookup], match(mid(Cell with filename in,7,3),Table1[Syntax],0))

It's a bit more complicated if not.

[alorotom]

Why not just add a couple of columns.

1 to replicate the original with text to columns and then another with a vlookup for whichever section is your abbrev based on a separate lookup table??

[louiebaby]

dibbers006 said:

Thanks so far for your thoughts.

Unfortunately, the codes appear at differing points in the filename.

And they range from 3-6 letters

Do they always have the first "-" before them, and the second "-" after them?

I could make this work with a bit of =FIND() action if so... (Or it might be =INSTRING() or something).

Let me know, and I'll knock it up for you.

[Neptune188]

dibbers006 said:

Thanks so far for your thoughts.

Unfortunately, the codes appear at differing points in the filename.

And they range from 3-6 letters

As long as there is a leading "-" before the lookup, it's actually not too hard.

Step 1: Add a column next to the filename. Formula to go in here (assume filename is in column B):

=RIGHT(B3,LEN(B3)-FIND("-",B3)) THIS WILL RETURN ALL LETTERS AFTER THE "-"

Step 2: Next to this column, put this formula in (assume previous column was Column C):

=left(C3,find("-",C3)-1) THIS WILL RETURN ALL LETTERS BEFORE THE "-"

This should give you the syntax so you can do the whole lookup thing as above.

N/B this is quick and dirty, there's probably a much tidier way of doing it than this.

[louiebaby]

Neptune188 said:

As long as there is a leading "-" before the lookup, it's actually not too hard.

Step 1: Add a column next to the filename. Formula to go in here (assume filename is in column B):

=RIGHT(B3,LEN(B3)-FIND("-",B3)) THIS WILL RETURN ALL LETTERS AFTER THE "-"

Step 2: Next to this column, put this formula in (assume previous column was Column C):

=left(C3,find("-",C3)-1) THIS WILL RETURN ALL LETTERS BEFORE THE "-"

This should give you the syntax so you can do the whole lookup thing as above.

N/B this is quick and dirty, there's probably a much tidier way of doing it than this.

I've gone for L2 as the cell, and assumed that we're finding whatever is between the first and second "-", but essentially the same as you:

=MID(L2,FIND("-",L2)+1,FIND("-",MID(L2,FIND("-",L2)+1,999))-1)

[louiebaby]

dibbers006 said:

Thanks for the offer.

I guess I should have given a better real world example. Here are three at random (slightly obfuscated) :

XXX-YY12345.A-7.5x4.5-DM-HTN-Test
AAXX7.A-DM-DBT-CVSTDC-InCam
XX120.A-7.5x4.5-DM-DBT-BCB-Support

The bold part is the part we need to pull out and remake against its full reference.

The location moves about which is why (I think) we'd need to search rather than simply pull from a location within the filename?

Looking at that, you're wanting to find the text between the second to last and last dash, is that true?

[louiebaby]

dibbers006 said:

Yes!

That could work actually...

It's a bit more complicated, do you have a special character that WON'T ever be used in the string?

[Neptune188]

Cracked it.

Create a table per my earlier post (Two columns with the syntax and lookup).

Formula you need is:

=Index(table1[lookup],match(true,isnumber(search(table1[syntax],B3)),0))

This is an array formula so press Ctrl+Shift+Enter when done.

Table1[lookup] is what you want to return
Table1[syntax] is what you are looking for in the filename
B3 is the filename.

Should work a charm.

[louiebaby]

dibbers006 said:

The filenames only ever contain . or - or _ if that's what you mean?

=MID(L2,FIND("~",SUBSTITUTE(L2,"-","~",LEN(L2)-LEN(SUBSTITUTE(L2,"-",""))-1))+1,(FIND("~",SUBSTITUTE(L2,"-","~",LEN(L2)-LEN(SUBSTITUTE(L2,"-","")))))-(FIND("~",SUBSTITUTE(L2,"-","~",LEN(L2)-LEN(SUBSTITUTE(L2,"-",""))-1))+1))

Replace "L2" with wherever your data is.

[sideways sid]

dibbers006 said:

Thanks for the offer.

I guess I should have given a better real world example. Here are three at random (slightly obfuscated) :

XXX-YY12345.A-7.5x4.5-DM-HTN-Test
AAXX7.A-DM-DBT-CVSTDC-InCam
XX120.A-7.5x4.5-DM-DBT-BCB-Support

The bold part is the part we need to pull out and remake against its full reference.

The location moves about which is why (I think) we'd need to search rather than simply pull from a location within the filename?

Some of those examples contain 4 hyphens, and some contain 5.

You need to use FIND, MID and RIGHT to find the text between the last hyphen and the one prior to it, then TRIM it. Then Use Index(Match) to return the syntax linked to the code in the table. Its probably easier to just do it and email it to you.

[louiebaby]

dibbers006 said:

Oh man, could you baby step me

I have my worksheet with the list of filenames...

The formula I have listed will return the bold bits you asked for. The filename was in cell L2 when I built the formula, so just copy my formula to where you want this, and change all the L2 to wherever the text can be found.

You can now wrap this in a Vlookup, which might look like this:

=VLOOKUP(

MID(L2,FIND("~",SUBSTITUTE(L2,"-","~",LEN(L2)-LEN(SUBSTITUTE(L2,"-",""))-1))+1,(FIND("~",SUBSTITUTE(L2,"-","~",LEN(L2)-LEN(SUBSTITUTE(L2,"-","")))))-(FIND("~",SUBSTITUTE(L2,"-","~",LEN(L2)-LEN(SUBSTITUTE(L2,"-",""))-1))+1)),

C: D,2,0)

The C: D bit is where your lookup table is. In column C would be text strings we've just found, D would contain what it means, EG LOL in C and Lots of Love in D.

[Neptune188]

My method is pretty simple - for each line it looks in your table of abbreviations and long names, and if it finds a match it'll return the long name. It doesn't much care for hyphens or other delimiters.

Screenshot below should make sense.



Send me the data and i'll do it for you.

Edited by Neptune188 on Tuesday 3rd September 14:06

[louiebaby]

Neptune188 said:

My method is pretty simple - for each line it looks in your table of abbreviations and long names, and if it finds a match it'll return the long name. It doesn't much care for hyphens or other delimiters.

Screenshot below should make sense.

Send me the data and i'll do it for you.

[Neptune188]

NB if you end up with millions of lines in the lookup table this will be very resource intensive. There's probably a tidier way of writing this in VBA.

To be fair there's also probably a tidier way of doing it than the spreadsheet i've bodged together, but I do working solutions not pretty ones.

[louiebaby]

dibbers006 said:

As to efficiency, this whole process will hopefully be part of something larger that claws us back man hours as it is, so a slightly less-then-optimal efficiency .XLSX is of no significance in the slightest!

.xlsb will make your file size smaller...

[Neptune188]

Glad I could help. Had to do something similar recently; was previously using a load of hidden columns to provide a string to a find query but this is much tidier.