Thanks David, that must have taken some doing. I am still a bit of a novice
and don't fully understand how to use it. I did paste it in into VB and it
just came up with many prompts to which i had to click "ok" to, then it
worked. But it wasn't automatic (for me anyway). I may be doing something
wrong !. I didn't realise it would be this much hassle for what would appear
to be a very simple "replace text" thing.

Anyway, your help was much appreciated. I'll read up on Macros and VBA then
i will try to apply the formula you gave me.

many thanks
Andrew


"David McRitchie" wrote in message
...
Hi Andy,
After looking at your actual data where the Flight numbers are
prefixed by 1 to 3 letters, I am even more convinced that you
could mess things up. But anyway everything is in Column A
so you could run the following macro or incorporate coding
or invoke the macro from your own.

Sub modify_airline_prefix()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long, cell As Range, airline As String
Columns("A:A").Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Columns("A:A"), _
Columns("A:A").SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
For i = 1 To Len(cell)
If Mid(cell.Value, i, 1) <= "9" Then GoTo donei
Next i
donei:
airline = UCase(Left(cell.Value, i - 1))
MsgBox airline & " -- " & cell.Value & " -- " & i
Select Case airline
Case "BA"
cell.Value = "BAW" & Mid(cell, 3)
Case "KL"
cell.Value = "KLM" & Mid(cell, 3)
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Andy100" wrote in message
...
Sorry i perhaps wasn't too clear. I have a "Web Query" style excel
sheet. It
actually gets its data from
http://www.newcastleinternational.co...tArrivals.aspx (flight
arrivals). However, their website uses fairly 'non-standard'
abbreviations
(as an arbitary example, it uses BD7674, whereas it should actually read
BMI7674, and so on).

So basically, the excel sheet forms the same kind of format that the
table
on their website does. i.e. Col A - Flight Number, Col B - Time Due In,
Col
C - From, Col D - Notes.

It is automatically refreshed (updated) every 5 minutes.

When it brings in the query (refreshes) from the website, i want it to
automatically look at a keylist of replacement words that i make up
(perhaps
on another sheet) and as soon as it sees one that needs replacing (BD to
BMI
in my example), then it automatically REPLACES it to read BMI7674 (as
opposed to BD7674) !

Maybe i'm being too fussy, i probably am !. But i thought it might just
be a
simple thing to do in excel, not sure !!

But it would make it a lot simpler for me than having to rename them
manually.

Many Thanks
Andrew


"David McRitchie" wrote in message
...
Hi Andrew,
You don't say exactly how automatically is to happen. How does
the data get into Excel -- is it through a macro. The implication was
that
you were not entering the data manually. So expect you have a
macro, and would add additional macro code to it.

Perhaps use of VLOOKUP in the macro after checking that the 3
character is a digit and not a letter from a previous change.
Application.WorksheetFunction.VLOOKUP(...

If you rename flight numbers, if you aren't making things more
ambiguous.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Andy100" wrote in message
...
Thanks for the speedy response Max, problem is i already have info
in
B1,
C1, D1 and E1. I was wanting a way of actually REPLACING the info in
column
1 (i.e. BA1234) and REPLACING it with (BAW1234), if you can see what
i'm
getting at.

Kind Regards
Andrew


"Max" wrote in message
...
Assuming the data is in col A, in A1 down,
and is identical in structure, e.g. you have

in A1: KL7746
in A2: BA1234
etc

then something like this might suffice

Put in B1:

=VLOOKUP(LEFT(TRIM(A1),2),{"KL","KLM";"BA","BAW"}, 2,0)&RIGHT(TRIM(A1),4)
Copy down

For the sample data, you'd get in B1:B2 :

KLM7746
BAW1234

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Andy100" wrote in message
...
How do i get excel to replace words from data imported from the
net.

Here is the problem. I am using excel to fetch data from a
flight
timetable,
however, when it says "KL" for the flight number, i want it to
correct
itself to "KLM". Also, when it says "BA", i want it to correct
itself to
"BAW".

i.e. "KL7746" would automatically change to "KLM7746".


Thanks in advance !


Regards
Andrew