Another plea for Excel help....

[hornet]

Can anyone help with this?

I need to run some sort of lookup command to extract specific text from a longer text string and return that text to the cell I specify (hope that makes sense).

I have 500 rows of text in column A that contain one of 15 document types (B4, B5, B6, B7 etc) within a longer text string - eg "Document B6 to be input". I want to be able to tell Excel to look at the overall text string, then look at the table of document types and if that text string contains one of those document types, return that document type reference to the correspnding cell in row B. In other words, look at the text in A1 and if it contains "B4" (or any other document ref from the table), put that as text in cell B1.

I've tried all the VLOOKUP and IF trickery I know (admittedly not a lot), but I can't figure it out. Any lookup wizards out there?

[Jay-Aim]

will need a macro that one I think with the doc number likely to be anywhere in the string

[Jinx]

Can't you use a combination of FIND, IF and MID functions e.g. =mid{<cell where text is>,find(<document name>,<cell where text is>},<length of document name>}this will return the report name in the text string. you should then be able to build up a few IF functions to cover all the report names?

>> Edited by Jinx on Monday 17th May 17:35 to replace curvy brackets with curly brackets as I didn't want smilies

>> Edited by Jinx on Monday 17th May 17:36

[Jay-Aim]

nested if's are limited to 7

[hornet]

Ok, we're getting there...

Can anyone tell me how to combine these two, as no matter what I try I keep getting #VALUE :-

=FIND(D1,A1)
=MID(A1,B1,2)

Those two steps are, I think, telling Excel to return the position of the text in cell D1 within the text string in A1, then telling it to go to that position and show me the text. That may sound a bit pointless with just one item, but ultimately I want to have it looking for 15 possibilities within about 500 rows of text - I need to figure out the basic formula before I have a crack at that!

I really want to do it all within one formula, but I can't combine them without the "not enough arguments" error. I need to add in or take out brackets somewhere....

[docevi1]

So you have something like:
A B
thisisafile.txt txt
thisisafile.txt doc
thisisafile.txt pdf
thisisafile.pdf
thisisafile.doc

and you want to check if the contents of column A contain any of those in column B? Is the format always the same, i.e. the extension are the last 3 characters?

I'm thinking this is a simple macro, would be easy to write if you can make the file available - mail me and I'll do it for you...

[hornet]

It's a list of outstanding reconciliation items with narratives containing what sort of document needs entering. I want something that can look at a table of 15 or so document types, scan down 500 lines of narrative and extract the document type from the longer text string.

Narratives look something like this :-

Rail transit - B6 to book
Storage gain - B7 to book
Kero dye - B0 to book

I want a formula (or macro) that can look at a table of document types (the B6/B7/B0 reference) and then enter that document type in the cell immediately to the left of the narrative so I can manipulate / subtotal the figures for reporting pruposes. Something like a SUMIF/COUNTIF function that returns the actual text from within the string rather than performing a sum/count. Sure it can be done!

[Jay-Aim]

1) easy macro - you can mail me if you wish

2) your error - =MID(A1,B1,2) is at B1 which should be a number (or a formula returning a number or enter a value in b1) as it's the start point to extract

[hornet]

Ok, have come up with...

=FIND(G2,D2,1)
=MID(D2,16,2)

Is there any way of combining those two into one step, or should I just try and do it via a macro instead? Ideally I want this to be looking for 15 possibilities in one hit.

[hornet]

Jay-Aim said:

1) easy macro - you can mail me if you wish

Might be an idea, as I'm going round in circles now! Do you have an email address I can drop an attachment to?

Cheers

[Jay-Aim]

hornet said:

Jay-Aim said: 1) easy macro - you can mail me if you wish Might be an idea, as I'm going round in circles now! Do you have an email address I can drop an attachment to? Cheers

Just click on my username and you can e-mail through there. I'll reply with an address for you to do an attachment

PS: can you confirm:
which version of office you are using
how quickly you need it back
If it is a one off or you will need to use this file time and again


>> Edited by Jay-Aim on Tuesday 18th May 10:01