Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract text strings from xls cells containing outlook items
Hi
I am exporting mail bodies from Outlook into column B in an xls sheet First four rows in each cell look like example below. Length of these rows vary. What I need is a macro that can loop throu rows and: - copy text "Username: ......." to col C - copy text "Error in: ......." to col D - copy text "Error message: .." to col E Only text after : would be perfect, but not neccesary (column B will be deleted when copying is completed) My programming "skills" are limited - any help would be highly appreciated Thank you! Ulf ----------------------------------- Error Caught in Application_Error event Username: 01234asdfg Error in: https://www.xxxxxxx.com/Main/Event/r...x?ItemId=12345 Error Message: Invalid length for a Base-64 char array. Stack Trace: at System.Convert.FromBase64String(String s) at System.Web.UI.ObjectStateFormatter.Deserialize(Str ing inputString) at System.Web.UI.ObjectStateFormatter.System.Web.UI.I StateFormatter.Deserialize(String serializedState) at System.Web.UI.Util.DeserializeWithAssert(IStateFor matter formatter, String serializedState) at System.Web.UI.HiddenFieldPageStatePersister.Load() TargetSite: Byte[] FromBase64String(System.String) ----------------------------------- This is the nice solution I found for exporting from Outlook to Excel http://blogs.techrepublic.com.com/msoffice/?p=744 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract text strings from xls cells containing outlook items
On Wed, 11 Feb 2009 08:15:01 -0800, ulfb
wrote: Hi I am exporting mail bodies from Outlook into column B in an xls sheet First four rows in each cell look like example below. Length of these rows vary. What I need is a macro that can loop throu rows and: - copy text "Username: ......." to col C - copy text "Error in: ......." to col D - copy text "Error message: .." to col E Only text after : would be perfect, but not neccesary (column B will be deleted when copying is completed) My programming "skills" are limited - any help would be highly appreciated Thank you! Ulf ----------------------------------- Error Caught in Application_Error event Username: 01234asdfg Error in: https://www.xxxxxxx.com/Main/Event/r...x?ItemId=12345 Error Message: Invalid length for a Base-64 char array. Stack Trace: at System.Convert.FromBase64String(String s) at System.Web.UI.ObjectStateFormatter.Deserialize(Str ing inputString) at System.Web.UI.ObjectStateFormatter.System.Web.UI. IStateFormatter.Deserialize(String serializedState) at System.Web.UI.Util.DeserializeWithAssert(IStateFor matter formatter, String serializedState) at System.Web.UI.HiddenFieldPageStatePersister.Load() TargetSite: Byte[] FromBase64String(System.String) ----------------------------------- This is the nice solution I found for exporting from Outlook to Excel http://blogs.techrepublic.com.com/msoffice/?p=744 Here's one solution using a User Defined Function (UDF) and Regular Expressions. It does assume, as you wrote, that ALL of the lines are in a single cell. It also assumes that the entire phrase to be extracted is on the same line in that cell. If the phrase could "run over" more than one line, we would need to know how to tell that in order to make appropriate changes. Set up your worksheet with labels in column 1. The label must be EXACTLY the word or words that precedes the colon (:). So, in your example: C1: Username D1: Error in E1: Error message Then enter the formula: C2: =ReExtr($B2,C$1) Fill right D2:E2 Then select C2:E2 and fill down as far as required. (This will initially return a #NAME! error) To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. ======================================= Option Explicit Function ReExtr(rg As Range, Extr As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.MultiLine = True re.ignorecase = True re.Global = True re.Pattern = "[\s\S]*" & Extr & ":\s*(.*)[\s\S]*" ReExtr = re.Replace(rg, "$1") End Function ================================ --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract text strings from xls cells containing outlook items
Thank you Ron - you solved my problem!
/ulf "Ron Rosenfeld" wrote: On Wed, 11 Feb 2009 08:15:01 -0800, ulfb wrote: Hi I am exporting mail bodies from Outlook into column B in an xls sheet First four rows in each cell look like example below. Length of these rows vary. What I need is a macro that can loop throu rows and: - copy text "Username: ......." to col C - copy text "Error in: ......." to col D - copy text "Error message: .." to col E Only text after : would be perfect, but not neccesary (column B will be deleted when copying is completed) My programming "skills" are limited - any help would be highly appreciated Thank you! Ulf ----------------------------------- Error Caught in Application_Error event Username: 01234asdfg Error in: https://www.xxxxxxx.com/Main/Event/r...x?ItemId=12345 Error Message: Invalid length for a Base-64 char array. Stack Trace: at System.Convert.FromBase64String(String s) at System.Web.UI.ObjectStateFormatter.Deserialize(Str ing inputString) at System.Web.UI.ObjectStateFormatter.System.Web.UI. IStateFormatter.Deserialize(String serializedState) at System.Web.UI.Util.DeserializeWithAssert(IStateFor matter formatter, String serializedState) at System.Web.UI.HiddenFieldPageStatePersister.Load() TargetSite: Byte[] FromBase64String(System.String) ----------------------------------- This is the nice solution I found for exporting from Outlook to Excel http://blogs.techrepublic.com.com/msoffice/?p=744 Here's one solution using a User Defined Function (UDF) and Regular Expressions. It does assume, as you wrote, that ALL of the lines are in a single cell. It also assumes that the entire phrase to be extracted is on the same line in that cell. If the phrase could "run over" more than one line, we would need to know how to tell that in order to make appropriate changes. Set up your worksheet with labels in column 1. The label must be EXACTLY the word or words that precedes the colon (:). So, in your example: C1: Username D1: Error in E1: Error message Then enter the formula: C2: =ReExtr($B2,C$1) Fill right D2:E2 Then select C2:E2 and fill down as far as required. (This will initially return a #NAME! error) To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. ======================================= Option Explicit Function ReExtr(rg As Range, Extr As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.MultiLine = True re.ignorecase = True re.Global = True re.Pattern = "[\s\S]*" & Extr & ":\s*(.*)[\s\S]*" ReExtr = re.Replace(rg, "$1") End Function ================================ --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract text strings from xls cells containing outlook items
On Wed, 11 Feb 2009 22:51:02 -0800, ulfb
wrote: Thank you Ron - you solved my problem! /ulf You're welcome. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Cells That Don't Contain the Following Text Strings | Excel Discussion (Misc queries) | |||
Comparing text strings in cells | Excel Discussion (Misc queries) | |||
Comparing text strings in cells | Excel Discussion (Misc queries) | |||
HOW TO EXTRACT STRINGS FROM CELLS | Excel Discussion (Misc queries) | |||
How to skip cells with text strings. | Excel Discussion (Misc queries) |