How to extract specific text from a worksheet

Hi all.

I'm a novice Excel'er here, but I came across something that I've not been able to google my way out, so I figured I'd post for some help.

I have a spreadsheet which is an extract from another application's DB, and it's just ugly in Excel. No nice neat columns etc. Data from some tables of the other application were just dumped into cells. Some of the data I need is scattered about the worksheet in various cells. There are no seperators that are common, no common length of text strings I could use to pull every X word etc.

The one common thread I am working with here, is the the data I need to extract ALL begins with the first 4 letters, and containers a total of 8 characters. For example:

A3 - The dogs collar had the name ABCD1234 on it
J9 - Today I found a dog collar at the store, and it read ABCD2345
c5 - It says ABCD3456

I'm trying to basically see if there is a way/function within excel that can go through all of the cells and extract anything that says ABCD???? (and the 4 wildcards after it)

The Find all function finds them all, but trying to just extract the data ABCD??? info.

Any thoughts? Thanks in advance for the help!

Try this. Sounds like you'll need to amend the range A1 etc.

Please Login or Register  to view this content.

Thank you VERY much..but quick question. I'm not 100% sure how to use this

Can you tell me where I'd need to put this info in at?

Wow, I'm hopeless lol

I hit Alt F11, found the worksheet on the left I'm working with. Not sure what I have to hi-light but I hit "THIS WORKSHEET" and pasted the info in, and when I run it I get an error

COMPILE ERROR:
Expected End Sub

If you're still having problems, post a sample workbook and I'll take a look.

Ahhh, I figured it out. I'm just dumb.. lol It's putting them into Column "O" and I had to (like I was told initially) amend A1 and provide a range for it to search...

VERY cool.

Let me ask one other question if I could. Lets just say, using the same dog collar scenario, and the only difference was that the names I wanted to extract maybe did not all begin with ABCD. Lets say they were completely random, but the common thread was that the naming convention I want to extract is all 4 letters 4 numbers like below, but random...

abcd1234
disw2452
fghi1211

etc

Is there a way to edit the first script to look for any words with 4 letters and numbers like the examples above?

Try changing the pattern line to this:

Please Login or Register  to view this content.

this has been working GREAT! I really DO appreciate the help. Quick question about this formula that I'm using now (first one provided and added the A-Z and 0-0)

What determines what cell this will output to? I have noticed if I have the data all in column A1 through A20, it will put the extract next to it.

For example

if A1 was abcd2345, then it puts it in B1, C1, D1, E1, F1 etc...till Z
if A2 was dmco2213, then it puts it in B2, C2, D2, E2, F2, etc...till Z
and so on.

Sometimes these are quite large, and I've tinkered around with trying to find what in here identifies it as the cell to start putting the extract into, but I can't figure it out.

I'll keep tryin to figure it out, but if someone knows, that would be VERY COOL! Thanks again for all the help. This is DEFINITELY gonna save me some serious time and labor as sometimes I get these with over 1k pieces of data I have to pull out!!

This line means it starts the output in the next cell to the right (Offset), and then continues to the right (Resize). Transpose turns the array from vertical to horizontal.

Please Login or Register  to view this content.

Thanks Steven. Out of curiosity, is there a simple way to alter that so it puts all the results into a designated column? Say I wanted all the results to display starting in G1, and go down (ie G2, G3 etc)

if A1 was abcd2345, then it puts it in B1, C1, D1, E1, F1 etc...till Z
if A2 was dmco2213, then it puts it in B2, C2, D2, E2, F2, etc...till Z

It should only do that if you have more than one matching block of characters in A1/A2. If that's the case, how would the output relate to the input?

Please Login or Register  to view this content.

I'm doing something wrong, and I think it's the A1 info I added. I took your "Ammend A1" to mean the first A1 was the first Cell you wanted the script to search, and the second A1 was the last, so I edited that statement as pictured above. How should they be properly edited?

I think that's the issue with it going into so many cells.

I was hoping to have this, if possible, take the data and designate where it puts it when I run it. Maybe like G1, G2, G3 etc

Yes, your amendment is not right. My initial code assumed your data were all in column A. So it depends on where your data actually are - can you attach a sample workbook which accurately reflects your actual dataset?

The data is sensative to a degree, so I can't. I'm sure that doesn't help much. The extracts that I get encompass MANY different rows/columns, not just one. It's basically a DB Extract and it has dozens of tables that extract into their own cells.

It's really very ugly.

In doing it the way I've been (all be it wrong) it appears to be putting this data off to the right. I suppose I could just keep doing it this way, scroll to the right until the extracted DB data stops, and then just collect them from any one of the extra rows... it seems to be working this way, unless you see a complication doing it that way?

You don't need to post your actual data, just some dummy data arranged in the same format.

The data can probably be output any way you like, but without seeing what you've got and what you want I can't envisage what needs to be done.

that's fair enough. I'll see if I can make up some dummy info later this afternoon and post it up. Thanks again for all the help!!