Seperate info in a cell
 

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)

Seperate info in a cell
 

On Sat, 19 Dec 2009 10:58:52 +0000, Petert
wrote:

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

Seperate info in a cell
 

On Sat, 19 Dec 2009 11:14:31 GMT, Lars-Åke Aspelin
wrote:

On Sat, 19 Dec 2009 10:58:52 +0000, Petert
wrote:

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


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)

Seperate info in a cell
 

On Sat, 19 Dec 2009 10:58:52 +0000, Petert
wrote:

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

Seperate info in a cell
 

On Sat, 19 Dec 2009 07:58:19 -0500, Ron Rosenfeld
wrote:

On Sat, 19 Dec 2009 10:58:52 +0000, Petert
wrote:

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

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)

Seperate info in a cell
 

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 (MVP - Excel)


"Petert" wrote in message
...
On Sat, 19 Dec 2009 07:58:19 -0500, Ron Rosenfeld
wrote:

On Sat, 19 Dec 2009 10:58:52 +0000, Petert

wrote:

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

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)

Seperate info in a cell
 

On Sun, 20 Dec 2009 01:59:38 -0500, "Rick Rothstein"
wrote:

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)

Seperate info in a cell
 

On Mon, 21 Dec 2009 10:04:34 +0000, Petert
wrote:

Ron,

Many thanks - that works perfectly

--
Cheers

Peter

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

Seperate info in a cell
 

On Sat, 19 Dec 2009 10:58:52 +0000, Petert
wrote:

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)