|
Find string in Data
Is it possible to find a string of data within a cell and extract the
next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards, Al. |
Find string in Data
That sounds very possible but for something like this it would be really
helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards, Al. |
Find string in Data
Hi Mike,
Please see below. Hope this helps and many thanks for your help with this. Cheers, Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kind regards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards, Al.- Hide quoted text - - Show quoted text - |
Find string in Data
Me again
there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers, Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kind regards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards, Al.- Hide quoted text - - Show quoted text - |
Find string in Data
Hi Mike,
Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers, Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kind regards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards, Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
I think you can use this subroutine on a per row basis...
Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message ups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers, Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kind regards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards, Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
will place the name Gil Rogers in B1 and David Walker in C1. In your code,
LOL... I should never work from memory... the names are Gil Walker and David Williams. Rick |
Find string in Data
I think you can use this subroutine on a per row basis...
Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. I modified the above code to protect against one form of sloppy typing... extra blank spaces. This code should handle that problem... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String Contents = Replace(Range("A1").Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Rick |
Find string in Data
Hi Rick,
Thanks loads for your help with this. Slightly confused though as my VBA knowledge isn't the best. Do I have the parts in brackets after the sub name? If so, when I try and run this as a macro Excel doesn't find this? Also where does this statement "GetAssignments Range("A1"), "The Assigned Individual has been changed to" need to go? Sorry for this but thanks loads for your help so far. Kind Regards, Al. On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)" wrote: I think you can use this subroutine on a per row basis... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message ups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers,Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kindregards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards,Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
I modified the above code to protect against one form of sloppy typing...
extra blank spaces. This code should handle that problem... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String Contents = Replace(Range("A1").Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For the archive record... there is an error in the above code... I accidentally left a hard-coded reference in the Range statement in the first line of code after the Dim statements. The "A1" reference should have been a reference to the passed in Cell argument. Here is the corrected code... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String Contents = Replace(Cell.Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Rick |
Find string in Data
The first argument inside the brackets is a reference to the cell you want
to parse and the second argument is the phrase you want to find in order to get the next two words after it. As for how to use the subroutine... well, that depends on how your project is set up. Do you have a user form, perhaps an ActiveX control on the spreadsheet, or maybe you want it to happen on its own. Here is an example (not tested thoroughly, but it seem to work) where the parsing takes place whenever anything is changed in column A (the column where I am assuming your 1000 lines of long text strings are located). On the sheet where the data is, right-click sheet tab and select View Code from the popup menu that appears. You will be taken to the code window for that worksheet. Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo FixEvents If ActiveCell.Column = 1 Then Application.EnableEvents = False GetAssignments Target, "The Assigned Individual has been changed to" Application.EnableEvents = True End If Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(Cell.Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Now, place the long sample text you posted earlier into cell A1. Because the newsreader turned the "soft" returns (vbLf) from the cell's text into "hard" returns (vbCrLf), straight pasting will put the various lines into different rows. For this example, what I did was to first delete the contents of cells A2 through A14, then click on cell A1 and select all of its text on the formula bar and then, finally, press Ctrl+V to paste the text into the cell directly. As soon as you do that, you should see the two names pop up in cells B1 and C1. I am presuming that however you now load the data into each cell of the column, doing so will automatically kick off the subroutine and parse the names out. Rick wrote in message ups.com... Hi Rick, Thanks loads for your help with this. Slightly confused though as my VBA knowledge isn't the best. Do I have the parts in brackets after the sub name? If so, when I try and run this as a macro Excel doesn't find this? Also where does this statement "GetAssignments Range("A1"), "The Assigned Individual has been changed to" need to go? Sorry for this but thanks loads for your help so far. Kind Regards, Al. On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)" wrote: I think you can use this subroutine on a per row basis... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message ups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers,Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kindregards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards,Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
Hi Rick,
Can't thank you enough for this, this is really appreciated. Works perfectly now with the Worksheet_change function. Thanks again for your help and your persistence in sorting this for me. Kind Regards, Al. On 27 Jun, 17:29, "Rick Rothstein \(MVP - VB\)" wrote: The first argument inside the brackets is a reference to the cell you want to parse and the second argument is the phrase you want to find in order to get the next two words after it. As for how to use the subroutine... well, that depends on how your project is set up. Do you have a user form, perhaps an ActiveX control on the spreadsheet, or maybe you want it to happen on its own. Here is an example (not tested thoroughly, but it seem to work) where the parsing takes place whenever anything is changed in column A (the column where I am assuming your 1000 lines of long text strings are located). On the sheet where the data is, right-click sheet tab and select View Code from the popup menu that appears. You will be taken to the code window for that worksheet. Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo FixEvents If ActiveCell.Column = 1 Then Application.EnableEvents = False GetAssignments Target, "The Assigned Individual has been changed to" Application.EnableEvents = True End If Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(Cell.Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Now, place the long sample text you posted earlier into cell A1. Because the newsreader turned the "soft" returns (vbLf) from the cell's text into "hard" returns (vbCrLf), straight pasting will put the various lines into different rows. For this example, what I did was to first delete the contents of cells A2 through A14, then click on cell A1 and select all of its text on the formula bar and then, finally, press Ctrl+V to paste the text into the cell directly. As soon as you do that, you should see the two names pop up in cells B1 and C1. I am presuming that however you now load the data into each cell of the column, doing so will automatically kick off the subroutine and parse the names out. Rick wrote in message ups.com... Hi Rick, Thanks loads for your help with this. Slightly confused though as my VBA knowledge isn't the best. Do I have the parts in brackets after the sub name? If so, when I try and run this as a macro Excel doesn't find this? Also where does this statement "GetAssignments Range("A1"), "The Assigned Individual has been changed to" need to go? Sorry for this but thanks loads for your help so far. KindRegards,Al. On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)" wrote: I think you can use this subroutine on a per row basis... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message roups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers,Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kindregards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards,Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
Hi Rick,
Sorry to bother you again. After populating the sheet now fully with the data there does appear to be a problem when the cells contain a lot of data. As a result of the amount of data within the cells the procedure doesn't capture the info in the cells that have loads of data. Is there anyway to strip out the data first where it's not before or after the string? Thanks again, Al. On 27 Jun, 17:29, "Rick Rothstein \(MVP - VB\)" wrote: The first argument inside the brackets is a reference to the cell you want to parse and the second argument is the phrase you want to find in order to get the next two words after it. As for how to use the subroutine... well, that depends on how your project is set up. Do you have a user form, perhaps an ActiveX control on the spreadsheet, or maybe you want it to happen on its own. Here is an example (not tested thoroughly, but it seem to work) where the parsing takes place whenever anything is changed in column A (the column where I am assuming your 1000 lines of long text strings are located). On the sheet where the data is, right-click sheet tab and select View Code from the popup menu that appears. You will be taken to the code window for that worksheet. Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo FixEvents If ActiveCell.Column = 1 Then Application.EnableEvents = False GetAssignments Target, "The Assigned Individual has been changed to" Application.EnableEvents = True End If Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(Cell.Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Now, place the long sample text you posted earlier into cell A1. Because the newsreader turned the "soft" returns (vbLf) from the cell's text into "hard" returns (vbCrLf), straight pasting will put the various lines into different rows. For this example, what I did was to first delete the contents of cells A2 through A14, then click on cell A1 and select all of its text on the formula bar and then, finally, press Ctrl+V to paste the text into the cell directly. As soon as you do that, you should see the two names pop up in cells B1 and C1. I am presuming that however you now load the data into each cell of the column, doing so will automatically kick off the subroutine and parse the names out. Rick wrote in message ups.com... Hi Rick, Thanks loads for your help with this. Slightly confused though as my VBA knowledge isn't the best. Do I have the parts in brackets after the sub name? If so, when I try and run this as a macro Excel doesn't find this? Also where does this statement "GetAssignments Range("A1"), "The Assigned Individual has been changed to" need to go? Sorry for this but thanks loads for your help so far. KindRegards,Al. On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)" wrote: I think you can use this subroutine on a per row basis... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message roups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers,Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kindregards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards,Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
Okay, time to step back and find out what you are doing and why. Your
question asking if there is a way to remove data says to me that you are loading the data into your cells as a convenience for the calculation and not because it needs to be displayed to the user. Is that true; is there a requirement that the data be displayed or not? I ask because it may be possible to process the data from its source rather than putting it in cells to be processed later. If you don't have to display the data, then can you tell me where you are getting it from? Any other information that you think might help us under stand what your spreadsheet is doing? Rick wrote in message ups.com... Hi Rick, Sorry to bother you again. After populating the sheet now fully with the data there does appear to be a problem when the cells contain a lot of data. As a result of the amount of data within the cells the procedure doesn't capture the info in the cells that have loads of data. Is there anyway to strip out the data first where it's not before or after the string? Thanks again, Al. On 27 Jun, 17:29, "Rick Rothstein \(MVP - VB\)" wrote: The first argument inside the brackets is a reference to the cell you want to parse and the second argument is the phrase you want to find in order to get the next two words after it. As for how to use the subroutine... well, that depends on how your project is set up. Do you have a user form, perhaps an ActiveX control on the spreadsheet, or maybe you want it to happen on its own. Here is an example (not tested thoroughly, but it seem to work) where the parsing takes place whenever anything is changed in column A (the column where I am assuming your 1000 lines of long text strings are located). On the sheet where the data is, right-click sheet tab and select View Code from the popup menu that appears. You will be taken to the code window for that worksheet. Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo FixEvents If ActiveCell.Column = 1 Then Application.EnableEvents = False GetAssignments Target, "The Assigned Individual has been changed to" Application.EnableEvents = True End If Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(Cell.Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Now, place the long sample text you posted earlier into cell A1. Because the newsreader turned the "soft" returns (vbLf) from the cell's text into "hard" returns (vbCrLf), straight pasting will put the various lines into different rows. For this example, what I did was to first delete the contents of cells A2 through A14, then click on cell A1 and select all of its text on the formula bar and then, finally, press Ctrl+V to paste the text into the cell directly. As soon as you do that, you should see the two names pop up in cells B1 and C1. I am presuming that however you now load the data into each cell of the column, doing so will automatically kick off the subroutine and parse the names out. Rick wrote in message ups.com... Hi Rick, Thanks loads for your help with this. Slightly confused though as my VBA knowledge isn't the best. Do I have the parts in brackets after the sub name? If so, when I try and run this as a macro Excel doesn't find this? Also where does this statement "GetAssignments Range("A1"), "The Assigned Individual has been changed to" need to go? Sorry for this but thanks loads for your help so far. KindRegards,Al. On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)" wrote: I think you can use this subroutine on a per row basis... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message roups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers,Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kindregards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards,Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
Hi Rick,
Basically this data is extracted from an internal developed system as a CSV file. This system is used for tracking incidents to our core systems, however what the system doesn't track very well is how many analysts have had an involvement in the resolution of the ticket. The only way that this can be done is to look for the statement "The Assigned Individual has been changed to" within column A as each time the analyst is changed a record is captured in this field within the system as text. All I need to have is an understanding per ticket of how many analysts have been involved in the resolution of the ticket. Hope this helps, however if you need any further information please let me know. Many Thanks again on this, Al. On 28 Jun, 05:06, "Rick Rothstein \(MVP - VB\)" wrote: Okay, time to step back and find out what you are doing and why. Your question asking if there is a way to remove data says to me that you are loading the data into your cells as a convenience for the calculation and not because it needs to be displayed to the user. Is that true; is there a requirement that the data be displayed or not? I ask because it may be possible to process the data from its source rather than putting it in cells to be processed later. If you don't have to display the data, then can you tell me where you are getting it from? Any other information that you think might help us under stand what your spreadsheet is doing? Rick wrote in message ups.com... Hi Rick, Sorry to bother you again. After populating the sheet now fully with the data there does appear to be a problem when the cells contain a lot of data. As a result of the amount of data within the cells the procedure doesn't capture the info in the cells that have loads of data. Is there anyway to strip out the data first where it's not before or after the string? Thanks again,Al. On 27 Jun, 17:29, "Rick Rothstein \(MVP - VB\)" wrote: The first argument inside the brackets is a reference to the cell you want to parse and the second argument is the phrase you want to find in order to get the next two words after it. As for how to use the subroutine... well, that depends on how your project is set up. Do you have a user form, perhaps an ActiveX control on the spreadsheet, or maybe you want it to happen on its own. Here is an example (not tested thoroughly, but it seem to work) where the parsing takes place whenever anything is changed in column A (the column where I am assuming your 1000 lines of long text strings are located). On the sheet where the data is, right-click sheet tab and select View Code from the popup menu that appears. You will be taken to the code window for that worksheet. Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo FixEvents If ActiveCell.Column = 1 Then Application.EnableEvents = False GetAssignments Target, "The Assigned Individual has been changed to" Application.EnableEvents = True End If Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(Cell.Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Now, place the long sample text you posted earlier into cell A1. Because the newsreader turned the "soft" returns (vbLf) from the cell's text into "hard" returns (vbCrLf), straight pasting will put the various lines into different rows. For this example, what I did was to first delete the contents of cells A2 through A14, then click on cell A1 and select all of its text on the formula bar and then, finally, press Ctrl+V to paste the text into the cell directly. As soon as you do that, you should see the two names pop up in cells B1 and C1. I am presuming that however you now load the data into each cell of the column, doing so will automatically kick off the subroutine and parse the names out. Rick wrote in message roups.com... Hi Rick, Thanks loads for your help with this. Slightly confused though as my VBA knowledge isn't the best. Do I have the parts in brackets after the sub name? If so, when I try and run this as a macro Excel doesn't find this? Also where does this statement "GetAssignments Range("A1"), "The Assigned Individual has been changed to" need to go? Sorry for this but thanks loads for your help so far. KindRegards,Al. On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)" wrote: I think you can use this subroutine on a per row basis... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message roups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers,Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kindregards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards,Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
How big (in megabytes) is the CSV file and what records does it contain?
What I am interested in with that last question is do you have a separate CSV file for each row that you were filling in your spreadsheet with or is there a single CSV file with all of the row's data in it? I'm guessing it is the latter; so, if it is, do the individual records (what goes in each row) have some kind of identifying text description that I can put in column A so that you will recognize what is what (is the date you show at the beginning of your sample sufficient)? Rick wrote in message ps.com... Hi Rick, Basically this data is extracted from an internal developed system as a CSV file. This system is used for tracking incidents to our core systems, however what the system doesn't track very well is how many analysts have had an involvement in the resolution of the ticket. The only way that this can be done is to look for the statement "The Assigned Individual has been changed to" within column A as each time the analyst is changed a record is captured in this field within the system as text. All I need to have is an understanding per ticket of how many analysts have been involved in the resolution of the ticket. Hope this helps, however if you need any further information please let me know. Many Thanks again on this, Al. On 28 Jun, 05:06, "Rick Rothstein \(MVP - VB\)" wrote: Okay, time to step back and find out what you are doing and why. Your question asking if there is a way to remove data says to me that you are loading the data into your cells as a convenience for the calculation and not because it needs to be displayed to the user. Is that true; is there a requirement that the data be displayed or not? I ask because it may be possible to process the data from its source rather than putting it in cells to be processed later. If you don't have to display the data, then can you tell me where you are getting it from? Any other information that you think might help us under stand what your spreadsheet is doing? Rick wrote in message ups.com... Hi Rick, Sorry to bother you again. After populating the sheet now fully with the data there does appear to be a problem when the cells contain a lot of data. As a result of the amount of data within the cells the procedure doesn't capture the info in the cells that have loads of data. Is there anyway to strip out the data first where it's not before or after the string? Thanks again,Al. On 27 Jun, 17:29, "Rick Rothstein \(MVP - VB\)" wrote: The first argument inside the brackets is a reference to the cell you want to parse and the second argument is the phrase you want to find in order to get the next two words after it. As for how to use the subroutine... well, that depends on how your project is set up. Do you have a user form, perhaps an ActiveX control on the spreadsheet, or maybe you want it to happen on its own. Here is an example (not tested thoroughly, but it seem to work) where the parsing takes place whenever anything is changed in column A (the column where I am assuming your 1000 lines of long text strings are located). On the sheet where the data is, right-click sheet tab and select View Code from the popup menu that appears. You will be taken to the code window for that worksheet. Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo FixEvents If ActiveCell.Column = 1 Then Application.EnableEvents = False GetAssignments Target, "The Assigned Individual has been changed to" Application.EnableEvents = True End If Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(Cell.Text, vbLf, " ") Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub Now, place the long sample text you posted earlier into cell A1. Because the newsreader turned the "soft" returns (vbLf) from the cell's text into "hard" returns (vbCrLf), straight pasting will put the various lines into different rows. For this example, what I did was to first delete the contents of cells A2 through A14, then click on cell A1 and select all of its text on the formula bar and then, finally, press Ctrl+V to paste the text into the cell directly. As soon as you do that, you should see the two names pop up in cells B1 and C1. I am presuming that however you now load the data into each cell of the column, doing so will automatically kick off the subroutine and parse the names out. Rick wrote in message roups.com... Hi Rick, Thanks loads for your help with this. Slightly confused though as my VBA knowledge isn't the best. Do I have the parts in brackets after the sub name? If so, when I try and run this as a macro Excel doesn't find this? Also where does this statement "GetAssignments Range("A1"), "The Assigned Individual has been changed to" need to go? Sorry for this but thanks loads for your help so far. KindRegards,Al. On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)" wrote: I think you can use this subroutine on a per row basis... Sub GetAssignments(Cell As Range, Phrase As String) Dim X As Long Dim Fields() As String Dim Words() As String Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") Cell.Offset(0, X).Value = Words(0) & " " & Words(1) Next End Sub For your given example, this call.... GetAssignments Range("A1"), "The Assigned Individual has been changed to" will place the name Gil Rogers in B1 and David Walker in C1. In your code, simply set up a loop through all the cells in your data column that have data in them and, on each loop, pass in the cell address and your find-phrase. Rick wrote in message roups.com... Hi Mike, Apologies, my attention to detail was not on the ball tonight. The proper text is "The Assigned Individual has been changed to". Thanks and apologies, should have checked this before posting. Al. On 26 Jun, 20:58, Mike H wrote: Me again there are) I want to pull out the next two words after "This has been assigned to". This text may appear more than once, therefore the second entry should be put in column C etc. etc... Baffled as to why my formula didn't work until i discovered (Should have checked first) the string you wanted to find doesn't exist in the text :) Please clarify Mike " wrote: Hi Mike, Please see below. Hope this helps and many thanks for your help with this. Cheers,Al. A1: 08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC: BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just to confirm, we did have a problem cancelling in the system (see my note below to Technical team). PO7500079598 has now been cancelled for you by Dave Williams. Kindregards Gill 08.03.07- tried to cancel order PO7500079598 on behalf of Mel (see email request from Mel) The Case has been Closed. Resolution Code: Updated By: Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill Walker Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to Gill Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case Assigned to David W Email Text: This case has been assigned to you, please view the details below: The Assigned Individual has been changed to David Williams On 26 Jun, 20:25, Mike H wrote: That sounds very possible but for something like this it would be really helpful to see a string of data. Mike " wrote: Is it possible to find a string of data within a cell and extract the next two words when it appears? I have a large amount of text within column A for approximately 1000 lines. In column B onwards (dependent upon how many unique entries I could do this for one entry using =FIND function however I'm not sure how to do this for multiple entries and also pull out the next two words as it's people's names so won't be a specific set of characters. Thanks in advance for your help with this. Regards,Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Find string in Data
The CSV averages around 3MB and will contain the following columns:
Case ID Create Date Created By Closed Date Details (max 500 chars) Resolution Details Diary Editor I then strip out the unwanted fields and make it so it is just column G and this will then be column A as per my original design and question. Each 'Case' has a new entry (and therefore new line) within the CSV so each row is unique. The identifying text would be 'Diary Editor'. For info' from checking the records every record does start with a date as well, however as you can see even within the cells different dates will appear throughout. Okay, good. Now, since this is a CSV file, I have assumed that any fields (the last one in particular) that have commas in them are enclosed in quote marks. Give the following a try... if I have done everything correctly (my test case worked fine; but, of course, that was based on a made up file), it will place the Case ID and Create Date in the first cell (in order for you to be able to identify which record is which) followed by the parsed out names in the columns following that. I chose to initiate the macro from a (Control Toolbox, not Forms) CommandButton placed on the worksheet (although you can use a UserForm with a CommandButton on it if you wish), but you can change that if you want. Here is the code behind the CommandButton's Click event... Private Sub CommandButton1_Click() Dim StartCell As Range Set StartCell = Range("A2") If StartCell.Text = "" Then ProcessFile "c:\temp\test.csv", StartCell End Sub Notice I chose A2 as the starting cell.... that is because I placed the CommandButton over cell A1. If you want the output to start in a different cell, just change the Set statement's reference to that cell. The ProcessFile routine, which is called from this Click event, first checks to make sure the starting cell is empty (no sense running the code if it has already been executed earlier); all you do is pass the CSV file (path and filename), using your path/filename in place of the test one shown above, that you want to process and the starting cell reference into it as arguments. Next, below my signature, is the code that makes everything happen. While you could put this code in the worksheet's code window, I placed it in a Module that I added to the project thus making it available to any worksheet in the workbook (in case you wanted to keep multiple files in one workbook on separate worksheets). There are three procedures that make up the Module code... the ProcessFile subroutine and two subroutines to help it out, one is a modification of the GetAssignments subroutine I posted earlier (modified to work within the structure I have created here) and the other is a Split type function that will ignore the delimiter character if it occurs within a quoted piece of text. Hopefully, I made no mistakes and you can simply copy/paste everything into the appropriate locations and go. Let me know if it worked or if you have any problems with it. Rick Sub ProcessFile(PathandFileName As String, Target As Range) Dim X As Long Dim FF As Integer Dim TotalFile As String Dim Fields() As String Dim Records() As String FF = FreeFile Open PathandFileName For Binary As #FF TotalFile = Space(LOF(FF)) Get #FF, , TotalFile Close #FF On Error GoTo FixEvents Records = Split(TotalFile, vbNewLine) For X = 0 To UBound(Records) Fields = SplitAroundQuotes(Records(X)) Cells(Target.Row + X, Target.Column).Value = _ Fields(0) & " (" & Fields(1) & ")" Application.EnableEvents = False GetAssignments Target.Offset(X, 0), Fields(UBound(Fields)), _ "The Assigned Individual has been changed to" Application.EnableEvents = True Next Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(StartCell As Range, _ DiaryText As String, _ Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(DiaryText, vbLf, " ") End If Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") StartCell.Offset(0, X).Value = Words(0) & " " & _ Replace(Words(1), """", "") Next End Sub Function SplitAroundQuotes(TextToSplit As String, _ Optional Delimiter As String = ",") As String() Dim X As Long Dim QuoteDelimited() As String Dim WorkingArray() As String QuoteDelimited = Split(TextToSplit, """") For X = 1 To UBound(QuoteDelimited) Step 2 QuoteDelimited(X) = Replace$(QuoteDelimited(X), _ Delimiter, Chr$(0)) Next TextToSplit = Join(QuoteDelimited, """") TextToSplit = Replace$(TextToSplit, Delimiter, Chr$(1)) TextToSplit = Replace$(TextToSplit, Chr$(0), Delimiter) WorkingArray = Split(TextToSplit, Chr$(1)) SplitAroundQuotes = WorkingArray End Function |
Find string in Data
Hi Rick,
I cannot thank you enough for this, this appears to work brilliantly. Really appreciate the time and effort you've put into this, thanks so much. Al. On 28 Jun, 17:09, "Rick Rothstein \(MVP - VB\)" wrote: The CSV averages around 3MB and will contain the following columns: Case ID Create Date Created By Closed Date Details (max 500 chars) Resolution Details Diary Editor I then strip out the unwanted fields and make it so it is just column G and this will then be column A as per my original design and question. Each 'Case' has a new entry (and therefore new line) within the CSV so each row is unique. The identifying text would be 'Diary Editor'. For info' from checking the records every record does start with a date as well, however as you can see even within the cells different dates will appear throughout. Okay, good. Now, since this is a CSV file, I have assumed that any fields (the last one in particular) that have commas in them are enclosed in quote marks. Give the following a try... if I have done everything correctly (my test case worked fine; but, of course, that was based on a made up file), it will place the Case ID and Create Date in the first cell (in order for you to be able to identify which record is which) followed by the parsed out names in the columns following that. I chose to initiate the macro from a (Control Toolbox, not Forms) CommandButton placed on the worksheet (although you can use a UserForm with a CommandButton on it if you wish), but you can change that if you want. Here is the code behind the CommandButton's Click event... Private Sub CommandButton1_Click() Dim StartCell As Range Set StartCell = Range("A2") If StartCell.Text = "" Then ProcessFile "c:\temp\test.csv", StartCell End Sub Notice I chose A2 as the starting cell.... that is because I placed the CommandButton over cell A1. If you want the output to start in a different cell, just change the Set statement's reference to that cell. The ProcessFile routine, which is called from this Click event, first checks to make sure the starting cell is empty (no sense running the code if it has already been executed earlier); all you do is pass the CSV file (path and filename), using your path/filename in place of the test one shown above, that you want to process and the starting cell reference into it as arguments. Next, below my signature, is the code that makes everything happen. While you could put this code in the worksheet's code window, I placed it in a Module that I added to the project thus making it available to any worksheet in the workbook (in case you wanted to keep multiple files in one workbook on separate worksheets). There are three procedures that make up the Module code... the ProcessFile subroutine and two subroutines to help it out, one is a modification of the GetAssignments subroutine I posted earlier (modified to work within the structure I have created here) and the other is a Split type function that will ignore the delimiter character if it occurs within a quoted piece of text. Hopefully, I made no mistakes and you can simply copy/paste everything into the appropriate locations and go. Let me know if it worked or if you have any problems with it. Rick Sub ProcessFile(PathandFileName As String, Target As Range) Dim X As Long Dim FF As Integer Dim TotalFile As String Dim Fields() As String Dim Records() As String FF = FreeFile Open PathandFileName For Binary As #FF TotalFile = Space(LOF(FF)) Get #FF, , TotalFile Close #FF On Error GoTo FixEvents Records = Split(TotalFile, vbNewLine) For X = 0 To UBound(Records) Fields = SplitAroundQuotes(Records(X)) Cells(Target.Row + X, Target.Column).Value = _ Fields(0) & " (" & Fields(1) & ")" Application.EnableEvents = False GetAssignments Target.Offset(X, 0), Fields(UBound(Fields)), _ "The Assigned Individual has been changed to" Application.EnableEvents = True Next Exit Sub FixEvents: Application.EnableEvents = True End Sub Sub GetAssignments(StartCell As Range, _ DiaryText As String, _ Phrase As String) Dim X As Long Dim Contents As String Dim Fields() As String Dim Words() As String If Not IsNull(Contents) Then Contents = Replace(DiaryText, vbLf, " ") End If Do While InStr(Contents, " ") Contents = Replace(Contents, " ", " ") Loop Fields = Split(Contents, Phrase) For X = 1 To UBound(Fields) Words = Split(Trim$(Fields(X)), " ") StartCell.Offset(0, X).Value = Words(0) & " " & _ Replace(Words(1), """", "") Next End Sub Function SplitAroundQuotes(TextToSplit As String, _ Optional Delimiter As String = ",") As String() Dim X As Long Dim QuoteDelimited() As String Dim WorkingArray() As String QuoteDelimited = Split(TextToSplit, """") For X = 1 To UBound(QuoteDelimited) Step 2 QuoteDelimited(X) = Replace$(QuoteDelimited(X), _ Delimiter, Chr$(0)) Next TextToSplit = Join(QuoteDelimited, """") TextToSplit = Replace$(TextToSplit, Delimiter, Chr$(1)) TextToSplit = Replace$(TextToSplit, Chr$(0), Delimiter) WorkingArray = Split(TextToSplit, Chr$(1)) SplitAroundQuotes = WorkingArray End Function- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 05:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com