+ Reply to Thread
Results 1 to 14 of 14

Excel to replace words ?

  1. #1
    Andy100
    Guest

    Excel to replace words ?

    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



  2. #2
    Max
    Guest

    Re: Excel to replace words ?

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >




  3. #3
    Andy100
    Guest

    Re: Excel to replace words ?

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > >
    > >

    >
    >




  4. #4
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Quote Originally Posted by 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
    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....................
    Paul

  5. #5
    Max
    Guest

    Re: Excel to replace words ?

    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.
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "Andy100" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  6. #6
    David McRitchie
    Guest

    Re: Excel to replace words ?

    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" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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
    > > >
    > > >

    > >
    > >

    >
    >




  7. #7
    Gary's Student
    Guest

    Re: Excel to replace words ?

    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
    --
    Gary's Student


    "Paul Sheppard" wrote:

    >
    > Andy100 Wrote:
    > > 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....................
    >
    >
    > --
    > Paul Sheppard
    >
    >
    > ------------------------------------------------------------------------
    > Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
    > View this thread: http://www.excelforum.com/showthread...hreadid=399812
    >
    >


  8. #8
    Andy100
    Guest

    Re: Excel to replace words ?

    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" <[email protected]> wrote in message
    news:#[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




  9. #9
    Andy100
    Guest

    Re: Excel to replace words ?

    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

    "Gary's Student" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > --
    > Gary's Student
    >
    >
    > "Paul Sheppard" wrote:
    >
    > >
    > > Andy100 Wrote:
    > > > 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....................
    > >
    > >
    > > --
    > > Paul Sheppard
    > >
    > >
    > > ------------------------------------------------------------------------
    > > Paul Sheppard's Profile:

    http://www.excelforum.com/member.php...o&userid=24783
    > > View this thread:

    http://www.excelforum.com/showthread...hreadid=399812
    > >
    > >




  10. #10
    David McRitchie
    Guest

    Re: Excel to replace words ?

    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" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:#[email protected]...
    > > 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" <[email protected]> wrote in message

    > news:[email protected]...
    > > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 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
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




  11. #11
    David McRitchie
    Guest

    Re: Excel to replace words ?

    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.
    ---
    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" <[email protected]> wrote in message news:[email protected]...
    > 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
    >
    > "Gary's Student" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > > --
    > > Gary's Student
    > >
    > >
    > > "Paul Sheppard" wrote:
    > >
    > > >
    > > > Andy100 Wrote:
    > > > > 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....................
    > > >
    > > >
    > > > --
    > > > Paul Sheppard
    > > >
    > > >
    > > > ------------------------------------------------------------------------
    > > > Paul Sheppard's Profile:

    > http://www.excelforum.com/member.php...o&userid=24783
    > > > View this thread:

    > http://www.excelforum.com/showthread...hreadid=399812
    > > >
    > > >

    >
    >




  12. #12
    Andy100
    Guest

    Re: Excel to replace words ?

    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" <[email protected]> wrote in message
    news:e9WeLV#[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:#[email protected]...
    > > > 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" <[email protected]> wrote in message

    > > news:[email protected]...
    > > > > 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 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" <[email protected]> wrote in message
    > > > > > news:[email protected]...
    > > > > > > 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
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




  13. #13
    David McRitchie
    Guest

    Re: Excel to replace words ?

    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.
    ---
    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" <[email protected]> wrote in message news:[email protected]...
    > 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.




  14. #14
    Andy100
    Guest

    Re: Excel to replace words ?

    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



    "David McRitchie" <[email protected]> wrote in message
    news:uzoNSn#[email protected]...
    > 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.
    > ---
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > > 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.

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1