Seperate info in a cell

  • Thread starter Thread starter Petert
  • Start date Start date
P

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)
 
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
Click to expand...

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
 
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
Click to expand...


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)
 
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
Click to expand...

Does A1 contain that data as a text string?

Or is it an Excel date/time formatted to look like that?
--ron
 
Does A1 contain that data as a text string?

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

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)
 
Hi Ron,

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

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
 
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
 
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
Click to expand...

Ron,

Many thanks - that works perfectly

--
Cheers

Peter

(Reply to address is a spam trap - pse reply to the group)
 
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
Click to expand...

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)
 
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
Click to expand...

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)
 
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.
Click to expand...

Try changing

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

Hope this helps / Lars-Åke
 
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.
Click to expand...

========================
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
 
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
Click to expand...

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)
 
Back
Top