Automatically Detecting and Erasing rows

**anrivel** · 11-21-2007, 05:03 PM

My situation is:

I have the following:

INDIANAPOLIS,IN (IND) 19:40 DALLAS, TX (DFW) 21:00 AA507 M83 2:20
JACKSONVILLE,FL (JAX) XXXX BOSTON, MA (BOS) XXXX XXXXX XXX

As you can see, there are 7 columns in the first row and 6 in the second one. First row:
Departure NAME and ID, Departure Time, Arrival NAME and ID,Arrival Time, Flight ID, Equipment,and Time. The above is just the two cases the spreadsheet can present, but there are more than 2,000 rows with flight information.

For the first row , I would like to:

1. Extract or copy the 3 letters identification (in this case IND)
2. Create another column, and insert these 3 letters
3. Erase, if able, the (XXX) letters.
4. Do the same for the third column.

This will create two more columns.

Now, for the second row, I must be able to 'detect' the XXXX and erase the whole row.

Any help is greatly appreciated.

PD The purpose of this is to extract the OneWorld Alliance Timetable
into a CSV (comma separated values), in order to use it in a website database.

**jasoncw** · 11-21-2007, 05:58 PM

Here you go, try using this procedure:

Please Login or Register  to view this content.

HTH

Jason

BTW, nice plug at the end of your post...

LOL

**RockiesMan** · 11-21-2007, 06:14 PM

You can do this formulas... VB can do this too, and I started telling you how, but it was taking much too long to look up the functions and type the code. Would be trivial if I knew VB functions by heart, but anyway, the attached workbook does what you want. Rows with x's become blank, and the codes are extracted as you wanted.

Fill all the rows with these formula. Tell me if you don't know how to do this quickly.

After this is done, just delete the original columns and sort to get rid of empty ones before saving as csv.

If you really, really want a code solution, I will help you with that.

HTH,

BW

**anrivel** · 11-21-2007, 08:12 PM

Thanks, it works effectively the problem. No need for serious code.

How much I have to pay?

LOL

**RockiesMan** · 11-21-2007, 08:34 PM

Well, you could use the code now that it was kindly provided to you by Jason. He must be a really fast at this stuff; it was a pretty fast reply.

I have coded some complex stuff, but never studied it enough, always have to look up functions and syntax.

Formulas are a great place to start, because you test fairly complex stuff before coding, and also it is better than code if you are only doing something a couple of times. Hopefully you will look at those formulas and try to understand how they work; I will explain if you have questions.

That will be $50

BW

**jasoncw** · 11-21-2007, 09:11 PM

Yes, it could definitely all be easily done with formulas. However, based on your request, it sounded like you wanted an automated process. Free of charge.

Jason