Seperate info in a cell

[Petert]

Hi,

In cell A1 I have the following information:

Fri 30 Oct 09:11

What I would like to do, via a macro I imagine, is to delete the month
and to seprate out the time into cell B1, so I would end up with:

Cell A1 - Fri 30

Cell B1 - 09:11

Any suggestions as to how I should achieve this would be much
appreciated

--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)

 

[Lars-Åke Aspelin]

Hi,

In cell A1 I have the following information:

Fri 30 Oct 09:11

What I would like to do, via a macro I imagine, is to delete the month
and to seprate out the time into cell B1, so I would end up with:

Cell A1 - Fri 30

Cell B1 - 09:11

Any suggestions as to how I should achieve this would be much
appreciated

Try this macro:

Sub separate()
With ActiveSheet
blankpos1 = InStr(.Cells(1, "A"), " ")
blankpos2 = InStr(blankpos1 + 1, .Cells(1, "A"), " ")
blankpos3 = InStr(blankpos2 + 1, .Cells(1, "A"), " ")
.Cells(1, "B") = Mid(.Cells(1, "A"), blankpos3 + 1, 5)
.Cells(1, "A") = Left(.Cells(1, "A"), blankpos2 - 1)
End With
End Sub

Hope this helps / Lars-Åke

 

[Petert]

Try this macro:

Sub separate()
With ActiveSheet
blankpos1 = InStr(.Cells(1, "A"), " ")
blankpos2 = InStr(blankpos1 + 1, .Cells(1, "A"), " ")
blankpos3 = InStr(blankpos2 + 1, .Cells(1, "A"), " ")
.Cells(1, "B") = Mid(.Cells(1, "A"), blankpos3 + 1, 5)
.Cells(1, "A") = Left(.Cells(1, "A"), blankpos2 - 1)
End With
End Sub

Hope this helps / Lars-Åke

Hi Lars,

Yes this does help - a little!

I should have been more specific in my original post!!

Similar info that I have in cell A1 is also found in a cells in column
A down to perhaps a maximum of 100 cells - the exact number of cells
varies every month, but however many there are there is always
information in each one.

There is also info in columns B to E

What I did was to add a line to your macro that inserted a blank
column to the right of column A, but I now get an error message -
column B is inserted but everthing is copied from col A to col B

The error message is:

Run Time Error
Invalid procedure call or argument

Debug show the last line of the macro highlighted in yellow

..Cells(1, "A") = Left(.Cells(1, "A"), blankpos2 - 1)



--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)

 

[Ron Rosenfeld]

Hi,

In cell A1 I have the following information:

Fri 30 Oct 09:11

What I would like to do, via a macro I imagine, is to delete the month
and to seprate out the time into cell B1, so I would end up with:

Cell A1 - Fri 30

Cell B1 - 09:11

Any suggestions as to how I should achieve this would be much
appreciated

Does A1 contain that data as a text string?

Or is it an Excel date/time formatted to look like that?
--ron

 

[Petert]

Does A1 contain that data as a text string?

Or is it an Excel date/time formatted to look like that?
--ron

Hi Ron,

It's data I downloded from the Vodafone website - it's my bill. I
understand it's saved as a CSV file
--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)

 

[Ron Rosenfeld]

Hi Ron,

It's data I downloded from the Vodafone website - it's my bill. I
understand it's saved as a CSV file

Try this Macro

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

It will select everything in column A as written, but can be modified if
necessary.

=====================================
Option Explicit
Sub ParseDateTime()
Dim c As Range, rg As Range
Dim a

Set rg = Cells(Cells.Rows.Count, 1)
Set rg = Range("A1", rg.End(xlUp))
rg.EntireColumn.Insert

For Each c In rg
a = Split(c)
If UBound(a) = 3 Then
c.Offset(0, -1) = a(0) & " " & a(1)
c = a(3)
End If
Next c
End Sub
==============================
--ron

 

[Rick Rothstein]

If your date is in A1, then this macro will put the time in B1 and change
the contents of A1 to be the day name followed by the day number (as you
requested in your initial posting)...

Sub SplitDateInA1()
Range("B1").Value = TimeValue(Right(Range("A1").Value, 5))
Range("B1").NumberFormat = "hh:mm"
Range("A1").Value = Trim(Left(Range("A1").Value, 6))
End Sub

 

[Petert]

Try this Macro

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

It will select everything in column A as written, but can be modified if
necessary.

=====================================
Option Explicit
Sub ParseDateTime()
Dim c As Range, rg As Range
Dim a

Set rg = Cells(Cells.Rows.Count, 1)
Set rg = Range("A1", rg.End(xlUp))
rg.EntireColumn.Insert

For Each c In rg
a = Split(c)
If UBound(a) = 3 Then
c.Offset(0, -1) = a(0) & " " & a(1)
c = a(3)
End If
Next c
End Sub
==============================
--ron

Ron,

Many thanks - that works perfectly

--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)

 

[Petert]

If your date is in A1, then this macro will put the time in B1 and change
the contents of A1 to be the day name followed by the day number (as you
requested in your initial posting)...

Sub SplitDateInA1()
Range("B1").Value = TimeValue(Right(Range("A1").Value, 5))
Range("B1").NumberFormat = "hh:mm"
Range("A1").Value = Trim(Left(Range("A1").Value, 6))
End Sub

Rick,

Many thanks - I will be able to use this as a base for another task

--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)

 

[Ron Rosenfeld]

Ron,

Many thanks - that works perfectly

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

 

[Petert]

Try this Macro

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

It will select everything in column A as written, but can be modified if
necessary.

=====================================
Option Explicit
Sub ParseDateTime()
Dim c As Range, rg As Range
Dim a

Set rg = Cells(Cells.Rows.Count, 1)
Set rg = Range("A1", rg.End(xlUp))
rg.EntireColumn.Insert

For Each c In rg
a = Split(c)
If UBound(a) = 3 Then
c.Offset(0, -1) = a(0) & " " & a(1)
c = a(3)
End If
Next c
End Sub
==============================
--ron

Ron,

If I may be a pain and ask how the macro above needs to be altered to
seperate the info in column A to be:

A1: 30-Oct
B1: 09:11

As before, but instead of losing the Month, I lose the day instead.

--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)

 

[Lars-Åke Aspelin]

Ron,

If I may be a pain and ask how the macro above needs to be altered to
seperate the info in column A to be:

A1: 30-Oct
B1: 09:11

As before, but instead of losing the Month, I lose the day instead.

Try changing

a(0) & " " & a(1)
to
a(1) & "-" & a(2)

Hope this helps / Lars-Åke

 

[Ron Rosenfeld]

Ron,

If I may be a pain and ask how the macro above needs to be altered to
seperate the info in column A to be:

A1: 30-Oct
B1: 09:11

As before, but instead of losing the Month, I lose the day instead.

========================
Option Explicit
Sub ParseDateTime()
Dim c As Range, rg As Range
Dim a

Set rg = Cells(Cells.Rows.Count, 1)
Set rg = Range("A1", rg.End(xlUp))
rg.EntireColumn.Insert

For Each c In rg
a = Split(c)
If UBound(a) = 3 Then
c.Offset(0, -1) = a(1) & "-" & a(2)
c = a(3)
End If
Next c
End Sub
==========================

The array "a" has each <space> separated item in the string in a separate
element. So it's merely a matter of combining the elements you want.

Since you now want the 2nd and 3rd words, separated by a hyphen, you change the
line that concatenates those two elements.

Note also that the first element is element "0" and not element "1".
--ron

 

[Petert]

Hi,

In cell A1 I have the following information:

Fri 30 Oct 09:11

What I would like to do, via a macro I imagine, is to delete the month
and to seprate out the time into cell B1, so I would end up with:

Cell A1 - Fri 30

Cell B1 - 09:11

Any suggestions as to how I should achieve this would be much
appreciated

many thanks to all that took the time to reply. The problem is now
solved!!!

--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)