Removing Data prior to a Colon running a Macro

Marsman

Marsman

Board Regular
Joined
May 13, 2013
Messages
62
Office Version
  1. 365
Platform
  1. Windows
how would i set my script to remove all the data prior to the Colon in this macro?



Email Example:

[TABLE="width: 377"]
<tbody>[TR]
[TD]Hahahah[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Please be sure to check the API Results below for errors.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Thank you![/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]API Results : API Call Successful Status OK[/TD]
[/TR]
[TR]
[TD]Power Supply Node : 2C06[/TD]
[/TR]
[TR]
[TD]MAC Address : 00:00:08:14:00:00[/TD]
[/TR]
[TR]
[TD]Power Supply Type : StandBy[/TD]
[/TR]
[TR]
[TD]Power Supply Name : 2C06_-A[/TD]
[/TR]
[TR]
[TD]Power Supply Number : A[/TD]
[/TR]
[TR]
[TD]No. of Batteries : 1 String Of 3[/TD]
[/TR]
[TR]
[TD]AC Output Voltage Rating : 90VAC[/TD]
[/TR]
[TR]
[TD]House Number :[/TD]
[/TR]
[TR]
[TD]Address : Elm St[/TD]
[/TR]
[TR]
[TD]City : Smalltown[/TD]
[/TR]
[TR]
[TD]State : DM[/TD]
[/TR]
[TR]
[TD]Zip : XXXXX[/TD]
[/TR]
[TR]
[TD]Latitude : 27.7509[/TD]
[/TR]
[TR]
[TD]Longitude : -83.8767[/TD]
[/TR]
</tbody>[/TABLE]



With Mysheet
.Range("A1") = "Subject"
.Range("B1") = "Received"
.Range("D1") = "Category"
.Range("E1") = "Continuity API Results"
.Range("F1") = "Power Supply Node"
.Range("G1") = "MAC ADDRESS"
.Range("H1") = "POWER SUPPLY TYPE"
.Range("I1") = "POWER SUPPLY NAME"
.Range("J1") = "POWER SUPPLY NUMBER"
.Range("K1") = "NO. OF BATTERIES"
.Range("L1") = "AC OUTPUT VOLTAGE RATING"
.Range("M1") = "HOUSE NUMBER"
.Range("N1") = "ADDRESS"
.Range("O1") = "CITY"
.Range("P1") = "STATE"
.Range("Q1") = "ZIP"
.Range("R1") = "LATITUDE"
.Range("S1") = "LONGITUDE"




End With


For Each Item In myfolder.Items
Set x = Item
With Mysheet
Lines = Split(x.Body, Chr(10))


.Range("A" & MyRow) = x.Subject
.Range("B" & MyRow) = Format(x.ReceivedTime, "DD/MM/YYYY")


.Range("D" & MyRow) = Lines(0)
.Range("E" & MyRow) = Lines(6)
.Range("F" & MyRow) = Lines(7)
.Range("G" & MyRow) = Lines(8)
.Range("H" & MyRow) = Lines(9)
.Range("I" & MyRow) = Lines(10)
.Range("J" & MyRow) = Lines(11)
.Range("K" & MyRow) = Lines(12)
.Range("L" & MyRow) = Lines(13)
.Range("M" & MyRow) = Lines(14)
.Range("N" & MyRow) = Lines(15)
.Range("O" & MyRow) = Lines(16)
.Range("P" & MyRow) = Lines(17)
.Range("Q" & MyRow) = Lines(18)
.Range("R" & MyRow) = Lines(19)
.Range("S" & MyRow) = Lines(20)


Export Result:

[TABLE="width: 3482"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Received[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD]Continuity API Results[/TD]
[TD]Power Supply Node[/TD]
[TD]MAC ADDRESS[/TD]
[TD]POWER SUPPLY TYPE[/TD]
[TD]POWER SUPPLY NAME[/TD]
[TD]POWER SUPPLY NUMBER[/TD]
[TD]NO. OF BATTERIES[/TD]
[TD]AC OUTPUT VOLTAGE RATING[/TD]
[TD]HOUSE NUMBER[/TD]
[TD]ADDRESS[/TD]
[TD]CITY[/TD]
[TD]STATE[/TD]
[TD]ZIP[/TD]
[TD]LATITUDE[/TD]
[TD]LONGITUDE[/TD]
[/TR]
[TR]
[TD]RE: Beltway_Power_Supply_Updates[/TD]
[TD]27/04/2017[/TD]
[TD][/TD]
[TD]Hahahah[/TD]
[TD]API Results : API Call Successful Status OK[/TD]
[TD]Power Supply Node : PR368[/TD]
[TD]MAC Address : 00:00:08:14:00:00[/TD]
[TD]Power Supply Type : StandBy[/TD]
[TD]Power Supply Name : 2C06_-A[/TD]
[TD]Power Supply Number : A[/TD]
[TD]No. of Batteries : 1 String Of 3[/TD]
[TD]AC Output Voltage Rating : 90VAC[/TD]
[TD]House Number :[/TD]
[TD]Address : Elm St[/TD]
[TD]City : Smalltown[/TD]
[TD]State : DM[/TD]
[TD]Zip : XXXXX[/TD]
[TD]Latitude : 27.7509[/TD]
[TD]Longitude : -83.8767[/TD]
[/TR]
</tbody>[/TABLE]



would like to see:

[TABLE="width: 3482"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Received[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD]Continuity API Results[/TD]
[TD]Power Supply Node[/TD]
[TD]MAC ADDRESS[/TD]
[TD]POWER SUPPLY TYPE[/TD]
[TD]POWER SUPPLY NAME[/TD]
[TD]POWER SUPPLY NUMBER[/TD]
[TD]NO. OF BATTERIES[/TD]
[TD]AC OUTPUT VOLTAGE RATING[/TD]
[TD]HOUSE NUMBER[/TD]
[TD]ADDRESS[/TD]
[TD]CITY[/TD]
[TD]STATE[/TD]
[TD]ZIP[/TD]
[TD]LATITUDE[/TD]
[TD]LONGITUDE[/TD]
[/TR]
[TR]
[TD]RE: Power_Supply_Updates[/TD]
[TD]27/04/2017[/TD]
[TD][/TD]
[TD]Hahahah[/TD]
[TD]API Call Successful Status OK[/TD]
[TD]PR368[/TD]
[TD]00:00:08:14:00:00[/TD]
[TD]StandBy[/TD]
[TD]2C06_-A[/TD]
[TD]A[/TD]
[TD]1 String Of 3[/TD]
[TD]90VAC[/TD]
[TD][/TD]
[TD]Elm St[/TD]
[TD]Smalltown[/TD]
[TD]DM[/TD]
[TD]XXXXX[/TD]
[TD="align: right"]27.7509[/TD]
[TD="align: right"]-83.8767

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Try if your value was in AW4

> =TRIM(TEXT(MID(AW4,FIND(":",AW4)+1,255),""))
 
Upvote 0
Where would I place that with in the code below?

Sub Exp_Email()


resp = MsgBox("Export details for all items in this folder? This may take some time and may appear unresponsive.", vbYesNo + vbQuestion)
If resp = vbNo Then
MsgBox "Cancelled", vbOKOnly + vbInformation, "Outlook"
Exit Sub
End If


Dim myfolder As Outlook.Folder
Dim MyExcel As Object
Dim Myworkbook As Object
Dim Mysheet As Object
Dim MyRow As Integer
Dim Lines As Variant


MyRow = 2


Set myfolder = Application.ActiveExplorer.CurrentFolder
Dim a As String


Set MyExcel = CreateObject("Excel.Application")
Set Myworkbook = MyExcel.Workbooks.Add


Set Mysheet = Myworkbook.sheets("Sheet1")


On Error Resume Next
Dim x As Variant


With Mysheet
.Range("A1") = "Subject"
.Range("B1") = "Received"
.Range("D1") = "Category"
.Range("E1") = "API Results"
.Range("F1") = "Power Supply Node"
.Range("G1") = "MAC ADDRESS"
.Range("H1") = "POWER SUPPLY TYPE"
.Range("I1") = "POWER SUPPLY NAME"
.Range("J1") = "POWER SUPPLY NUMBER"
.Range("K1") = "NO. OF BATTERIES"
.Range("L1") = "AC OUTPUT VOLTAGE RATING"
.Range("M1") = "HOUSE NUMBER"
.Range("N1") = "ADDRESS"
.Range("O1") = "CITY"
.Range("P1") = "STATE"
.Range("Q1") = "ZIP"
.Range("R1") = "LATITUDE"
.Range("S1") = "LONGITUDE"




End With


For Each Item In myfolder.Items
Set x = Item
With Mysheet
Lines = Split(x.Body, Chr(10))


.Range("A" & MyRow) = x.Subject
.Range("B" & MyRow) = Format(x.ReceivedTime, "DD/MM/YYYY")


.Range("D" & MyRow) = Lines(0)
.Range("E" & MyRow) = Lines(6)
.Range("F" & MyRow) = Lines(7)
.Range("G" & MyRow) = Lines(8)
.Range("H" & MyRow) = Lines(9)
.Range("I" & MyRow) = Lines(10)
.Range("J" & MyRow) = Lines(11)
.Range("K" & MyRow) = Lines(12)
.Range("L" & MyRow) = Lines(13)
.Range("M" & MyRow) = Lines(14)
.Range("N" & MyRow) = Lines(15)
.Range("O" & MyRow) = Lines(16)
.Range("P" & MyRow) = Lines(17)
.Range("Q" & MyRow) = Lines(18)
.Range("R" & MyRow) = Lines(19)
.Range("S" & MyRow) = Lines(20)




End With
MyRow = MyRow + 1
Next Item


MsgBox "Finished " & MyRow - 2 & " Records Exported"
MyExcel.Visible = True
'
Set MyExcel = Nothing
Set Myworkbook = Nothing
Set Mysheet = Nothing
Set x = Nothing




End Sub
 
Upvote 0
maually placing it in G7 and looking at F7, then macro recorder gives me > ActiveCell.FormulaR1C1 = "=TRIM(TEXT(MID(RC[-1],FIND("":"",RC[-1])+1,255),""""))"

I didn't take it beyond looking at > MAC Address : 00:00:08:14:00:00 and producing > 00:00:08:14:00:00
 
Upvote 0
For those rows where you don't want the info before the colon, you could try something like this:
Code: 
.Range("E" & MyRow) = Mid(Lines(6), InStr(Lines(6), ":")+1)
 
Upvote 0
that was the Code I was looking for Russell.

thanks all for your input.

- Darin
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Macro to re-format source data for physical address list
2
Replies
11
Views
961
Peter_SSs
Peter_SSs
N
  • Solved
Massive Data Analysis needing a helper column
Replies
9
Views
613
Fluff
Fluff
R
  • Question Question
run time error 91 object variable or with block not set - reference is not found in looping program
Replies
8
Views
3K
renewgeorgia
R
M
  • Question Question
macro to align input data
Replies
1
Views
786
maheshr68
M
D
  • Question Question
Excel freezes when processing large # of rows
Replies
4
Views
10K
cornflakegirl
cornflakegirl

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top