Need the help of a friendly Excel Guru!!

  montyburns 16:36 01 Oct 2006
Locked

Probably need a macro for this, which scuppers me from Square One!

I've got a Sony Ericsson P900, which stores loads of texts, but as there's nearly 1000 on there now, decided it's about time some went. Takes forever to go through them individually to decide which to keep and which to dump, but have manged to get them off into a .txt file, which I've now got as an Excel file. Trouble is, the data format is all wrong.

I want to have a column for date sent, one for time sent, another for sender, and a fourth for the message

Presently I have one column, with all the data in!

It goes like:-

From: Fred mobile
Date: Friday 29th September 2006 13:55
...
"Thanks for the text you sent earlier"
Blank row
Blank row
Blank row
From: Dave mobile
Date: Friday 29th September 2006 14:55
...
"Thought you'd like to see the text that Fred sent"

etc


I need some way to remove the "From: " and "Date: " (easy enough with "Find & Replace" I'd guess, but not wanting to do this yet as it marks the data) But then to put the date in Column One (format 20/9/2006), time in Column Two (format 14:55), sender in Column Three, and full message in Column Four

Is this going to be extraordinarily hard, or blindingly simple?!

Ta in advance for any help!

  VoG II 16:52 01 Oct 2006

This doesn't quite do everything that you want (yet) but try it. Run the macro from the sheet with the existing data. It will rewrite to a new sheet.

Code:

Sub fmt()
Dim InSht As Worksheet, Outsht As Worksheet
Dim LastRow, iRow, jRow, jCol
Application.ScreenUpdating = False
Set InSht = ActiveSheet
LastRow = InSht.Cells(Rows.Count, 1).End(xlUp).Row
Sheets.Add after:=InSht
Set Outsht = ActiveSheet
jRow = 1
jCol = 2
For iRow = 1 To LastRow
If Left(Trim(InSht.Cells(iRow, 1).Value), 5) = "From:" Then
jRow = jRow + 1
Outsht.Cells(jRow, 3).Value = InSht.Cells(iRow, 1).Value
ElseIf Left(Trim(InSht.Cells(iRow, 1).Value), 5) = "Date:" Then
Outsht.Cells(jRow, 1).Value = InSht.Cells(iRow, 1).Value
ElseIf (InSht.Cells(iRow, 1).Value) <> "" Then
Outsht.Cells(jRow, 4).Value = InSht.Cells(iRow, 1).Value
End If
Next iRow
Outsht.Columns("A:D").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

  montyburns 18:50 01 Oct 2006

That's cracking mate!

The only thing it doesn't do is take the time out of the date/time data! :-)

How do you do this sort of thing so quick and so well? I should have just emailed to you in the first place, but I didn't want to presume! ;-)

  VoG II 18:56 01 Oct 2006

Right, I just wanted to make sure that the basic thing worked. Try this (be sure to include the Option Base 1 statement).

Code:

Option Base 1

Sub fmt()
Dim InSht As Worksheet, Outsht As Worksheet
Dim LastRow, iRow, jRow
Application.ScreenUpdating = False
Set InSht = ActiveSheet
LastRow = InSht.Cells(Rows.Count, 1).End(xlUp).Row
Sheets.Add after:=InSht
Set Outsht = ActiveSheet
jRow = 1
Outsht.Range("A1").Value = "Date"
Outsht.Range("B1").Value = "Time"
Outsht.Range("C1").Value = "Sender"
Outsht.Range("D1").Value = "Message"
For iRow = 1 To LastRow
If Left(Trim(InSht.Cells(iRow, 1).Value), 5) = "From:" Then
jRow = jRow + 1
Outsht.Cells(jRow, 3).Value = Right(InSht.Cells(iRow, 1).Value, Len(InSht.Cells(iRow, 1)) - 6)
ElseIf Left(Trim(InSht.Cells(iRow, 1).Value), 5) = "Date:" Then
Outsht.Cells(jRow, 1).Value = Mid(InSht.Cells(iRow, 1).Value, 7, Len(InSht.Cells(iRow, 1).Value) - 12)
Call cvrt(Outsht.Cells(jRow, 1))
Outsht.Cells(jRow, 2).Value = Right(InSht.Cells(iRow, 1).Value, 5)
ElseIf (InSht.Cells(iRow, 1).Value) <> "" Then
Outsht.Cells(jRow, 4).Value = InSht.Cells(iRow, 1).Value
End If
Next iRow
Outsht.Columns("A:D").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

Private Sub cvrt(R As Range)
Dim olddate As String, newdate As String, day As String, month As String, year As String
Dim i As Integer, j As Integer
Dim PossM()
PossM = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
olddate = R.Value
i = InStr(olddate, " ")
olddate = Right(olddate, Len(olddate) - i)
i = InStr(olddate, " ")
day = Left(olddate, i - 3)
olddate = Right(olddate, Len(olddate) - i)
year = Right(olddate, 4)
olddate = Left(olddate, Len(olddate) - 5)
For j = 1 To 12
If olddate = PossM(j) Then
month = Format(j, "00")
Exit For
End If
Next j
newdate = day & "/" & month & "/" & year
R.Value = newdate
End Sub

  montyburns 19:07 01 Oct 2006

Fantastic!

I was messing about trying to extract the date with the correct format, ditto with the time, and remove the un-necessary bits from the first column.... And now you've sorted it all!

Many, many thanks! Worked like a charm, and it now means I have a database which I can work on, and at last free up some memory on my phone!

Well chuffed!!!!

  VoG II 20:18 01 Oct 2006

My pleasure - rainy Sunday afternoon and so on.

Please note that the 'date' entered in column A is not a date as Excel understands it but a text value. It would be possible to convert this to an Excel Date but this would require a bit more coding.

I think that the routine that formats the date might be better written using the VB Scripting Dictionary but I'm still trying to get my head round that.

Tick as resolved?

  montyburns 23:37 01 Oct 2006

Ticked! Sorted!

(and date is fine!)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?