Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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" 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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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
|
|||
|
|||
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 |
#9
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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 |
#11
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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
|
|||
|
|||
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 |
#13
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
HOW DO I DISPLAY NUMBERS AS WORDS IN EXCEL 2003? | Excel Discussion (Misc queries) | |||
How can I replace 2 of 5 characters within an cell in MS Excel? | Excel Worksheet Functions | |||
Using Excel, how do I replace cells containing blanks with nulls? | Excel Discussion (Misc queries) | |||
Replace function not working properly in Excel 2000 SP3 | Excel Worksheet Functions |