Excel to replace words ?

[Andy100]

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

 

[Max]

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 ..

 

[Andy100]

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

 

[Paul Sheppard]

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

Hi Andrew

Try highlighting the column with the flight codes then do Edit >
Replace

In find what type KL and in replace with type KLM
etc....................

 

[Max]

Think you'd need a sub to do it automatically, for which other folks versed
in vba could offer to you (I don't know). Hang around awhile.

In the interim, if the formula approach works ok, we could always fill the
formulas in an empty column to the right, say in col G. Then just copy col G
and do a paste special > values to overwrite col A. And then delete col G.
Its just a couple of steps.

 

[David McRitchie]

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.

 

[Guest]

Andy100:

Paul Sheppard's good suggestion can be automated if you have a number or
substitutions that have to be repeated periodically. Just by using the
Recorder>

Consider attacking: "Now is the time for all good men to come to the aid of
their party."

Sub Macro1()

Selection.Replace What:="now", Replacement:="won", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="is", Replacement:="si", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="the", Replacement:="eht", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="for", Replacement:="rof", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


Your substitutions should be just as easy using the Macro Recorder.

Good Luck

 

[Andy100]

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.uk/FlightArrivals.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

 

[Andy100]

I'm sorry, i'm relatively new to excel, i'm not sure what you mean by
"recorder" or how to use it. It seems a little complicated. Is there not an
easier way, such as "find and replace" or something ?

Thanks
Andrew

Andy100:

Paul Sheppard's good suggestion can be automated if you have a number or
substitutions that have to be repeated periodically. Just by using the
Recorder>

Consider attacking: "Now is the time for all good men to come to the aid of
their party."

Sub Macro1()

Selection.Replace What:="now", Replacement:="won", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="is", Replacement:="si", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="the", Replacement:="eht", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="for", Replacement:="rof", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


Your substitutions should be just as easy using the Macro Recorder.

Good Luck

 

[David McRitchie]

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

 

[David McRitchie]

When you indicated a query it was assumed you were using
a macro. Recording a macro is a way of finding instructions
that might be useful in writing your own macro. Very seldom
would you be able to use something directly out of the macro
recorder. I don't think you would be able to use output out
the macro recorder in your case because there is no saying
where BA might be found what if there was a TBA airline.

Anyway if you are not familiar with macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You definitely need a macro to what you are asking,
and if it is running every 5 minutes you must be running
a macro, or other program coding.

I'm sorry, i'm relatively new to excel, i'm not sure what you mean by
"recorder" or how to use it. It seems a little complicated. Is there not an
easier way, such as "find and replace" or something ?

Thanks
Andrew

Andy100:

Paul Sheppard's good suggestion can be automated if you have a number or
substitutions that have to be repeated periodically. Just by using the
Recorder>

Consider attacking: "Now is the time for all good men to come to the aid of
their party."

Sub Macro1()

Selection.Replace What:="now", Replacement:="won", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="is", Replacement:="si", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="the", Replacement:="eht", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="for", Replacement:="rof", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


Your substitutions should be just as easy using the Macro Recorder.

Good Luck

 

[Andy100]

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]

sorry about leaving my debugging code in there, I got mixed up
about what was to stop the prefix a letter or digit.

Remove the line of code that has MsgBox on it.

As far as being automatic goes it depends on how you get your
data in automatically because you want to add to that.

 

[Andy100]

I still don't fully understand. You are obviously much more knowledgeable in
Excel than i am (doesn't take much !). I am going to try and read up on VBA
and macros because i'll find them useful i think.

Incidentally, you might know the answer to one further question. On my works
PC (Excel 2000) when i want to "Sort" certain rows in a table i just click
on the "Command Button" from the "Control Toolbox", draw a command box and
then start the macro by pressing "sort" data and then end the macro. From
then on in, i can just simply click the little "Command Button" that i
'drew' and it runs the macro automatically. However, on my set up (i'm using
Excel 2002), when i do that all i get is a button which names itself
"Command Button 1" and it does NOT take me into the 'Record a new macro'
prompt, i.e. i can never seem to be able to assign a macro to the "Command
Button" in Excel 2002 like i can in Excel 2000.

Do you know why ?. Sorry for the extra question there, slightly related i
guess !


Kind Regards
Andrew