Extract text strings from xls cells containing outlook items

[ulfb]

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/reg.item.aspx?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(String inputString)

at
System.Web.UI.ObjectStateFormatter.System.Web.UI.IStateFormatter.Deserialize(String serializedState)

at System.Web.UI.Util.DeserializeWithAssert(IStateFormatter formatter,
String serializedState)

at System.Web.UI.HiddenFieldPageStatePersister.Load()



TargetSite: Byte[] FromBase64String(System.String)

 

[Ron Rosenfeld]

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/reg.item.aspx?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(String inputString)

at
System.Web.UI.ObjectStateFormatter.System.Web.UI.IStateFormatter.Deserialize(String serializedState)

at System.Web.UI.Util.DeserializeWithAssert(IStateFormatter formatter,
String serializedState)

at System.Web.UI.HiddenFieldPageStatePersister.Load()



TargetSite: Byte[] FromBase64String(System.String)

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

 

[ulfb]

Thank you Ron - you solved my problem!
/ulf

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/reg.item.aspx?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(String inputString)

at
System.Web.UI.ObjectStateFormatter.System.Web.UI.IStateFormatter.Deserialize(String serializedState)

at System.Web.UI.Util.DeserializeWithAssert(IStateFormatter formatter,
String serializedState)

at System.Web.UI.HiddenFieldPageStatePersister.Load()



TargetSite: Byte[] FromBase64String(System.String)

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

 

[Ron Rosenfeld]

Thank you Ron - you solved my problem!
/ulf

You're welcome. Glad to help. Thanks for the feedback.
--ron