Extracting Substring from Column of Data

Hi Everyone,

First of all, you guys are all awesome. I've learned a lot just from searching other stuff.

I do some computational biophysics research but am having trouble with some of the data processing because I need bits of information that are nestled inside large strings of text.

Here's an example of what my data might start out looking like:
\1

What I want are those "gi" numbers. The number begins after "gi|" and ends with "|".

However, part of the trouble I've found is that there are many | elsewhere in the string and the gi numbers can have variable length so that I can't just use mid and select out a certain number of characters after each "gi|".

Here's what I would hope to get as an output:
\1

I've attached a workbook that has three sheets (one with 10 rows of real data, and then the other two are simply the example dummy input and dummy output shown above). If I wasn't clear let me know.

As a note, my real data can have as many as several thousand rows of results so I don't know if that means I should be using a macro or if a worksheet function is still practical.

Thanks a ton if anyone is able to figure this out! The scientific world will benefit from your contribution

Here is a macro that will find all numbers you are looking for in a new sheet named Sheet1 that you have to create manually. You can also change the name of this sheet in the macro if you like.

Copy this code to a procedure in your workbook. Create the sheet Sheet1 and run it.

Please Login or Register  to view this content.

Originally Posted by p24leclerc

Here is a macro that will find all numbers you are looking for in a new sheet named Sheet1 that you have to create manually. You can also change the name of this sheet in the macro if you like.

Copy this code to a procedure in your workbook. Create the sheet Sheet1 and run it.

Please Login or Register  to view this content.

What a gem!! Thanks so much.