How do I get this data from email (Parse)

[Steve]

I receive emails with tab delimited data looking like this

9:46 ET AAA 100 BBBB CCC DDD [EEEEEEEE]

I'd like to grab this data directly from my email account and load into
Excel sheet.

ColumnA = 9:46 ET
ColumnB = AAA
ColumnC = 100
ColumnD = BBBB
ColumnE = CCC
ColumnF = DDD
ColumnG = EEEEEEEE

I think I understand how to connect to outlook but I don't know the
rest
What is the method to parse based on delimiter? Or how else can I do
this

Thoughts

Thanks
Steve

 

[Norman Jones]

Hi Steve,

Perhaps this will help:

'=============>>
Public Sub Tester001()
Dim sStr As String
Dim arr As Variant
Dim i As Long

sStr = "9:46 ET AAA 100 BBBB CCC DDD [EEEEEEEE]"

arr = Split(sStr, " ")

i = UBound(arr) - LBound(arr) + 1

Range("A1").Resize(1, i).Value = arr

End Sub
'<<=============

 

[Norman Jones]

Hi Steve,

I should have added that the Split function requires xl2k+.

If this constitutes a problem, post back.

 

[Norman Jones]

Hi Steve,

For a non-code solution, with the data selected, try:

Data | Text to Columns | Delimited | Select the Space delimiter | Next | ...

 

[Steve]

Norman,

When I run this - it only places the data in row1 and each message
afterwards is appended to the last column in row1.

I was looking to insert message 2 in row 2 and so on.

Is there a way to assign each element of the parsed string to a
variable. If so I think I can get to where I need to be.

Thanks Again
Steve

 

[Norman Jones]

Hi Steve,

Assuming that the raw data is in column A, try:

'=============>>
Public Sub Tester001()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim LRow As Long
Dim sStr As String
Dim arr As Variant
Dim i As Long

Application.ScreenUpdating = False

Set SH = ActiveSheet

LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row

Set rng = SH.Range("A1:A" & LRow)

For Each rCell In rng.Cells
sStr = rCell.Value
arr = Split(sStr, " ")
i = UBound(arr) - LBound(arr) + 1
rCell.Resize(1, i).Value = arr
Next rCell

Application.ScreenUpdating = True

End Sub
'<<=============