Separating text and dates.

[Mark K]

Hi all.

I have data imported from a csv file where one column (B) contains cells with text and date in the format of "XXXX dd/mm/yyyy". Originally was using text to columns to split these without a problem, using the space as the separator. But recently I've been coming across some where there is a space inside the text which is causing problems.

My current solution is to first search and replace the space in the text with a hyphen then do the text to columns. So far I have the macro seaching for and replacing 15 different text entries.

Just wondering if anyone had an easier way to do this?

I used to count back a fixed number of characters from the right (can't remember exactly how I did it) but then I found it was reversing the day and the month when separated.

NOTE: My current method works, just looking for a more efficient way of doing it.

TIA

[Dave Peterson]

You could use a couple of helper columns:

With that info in B1, you could get the text portion with:
=LEFT(B1,LEN(B1)-11)

And the date portion with:
=DATE(RIGHT(B1,4),MID(RIGHT(B1,10),4,2),MID(RIGHT(B1,10),1,2))



Mark K wrote:
>
> Hi all.
>
> I have data imported from a csv file where one column (B) contains
> cells with text and date in the format of "XXXX dd/mm/yyyy". Originally
> was using text to columns to split these without a problem, using the
> space as the separator. But recently I've been coming across some where
> there is a space inside the text which is causing problems.
>
> My current solution is to first search and replace the space in the
> text with a hyphen then do the text to columns. So far I have the macro
> seaching for and replacing 15 different text entries.
>
> Just wondering if anyone had an easier way to do this?
>
> I used to count back a fixed number of characters from the right (can't
> remember exactly how I did it) but then I found it was reversing the day
> and the month when separated.
>
> NOTE: My current method works, just looking for a more efficient way of
> doing it.
>
> TIA
>
> --
> Mark K
> ------------------------------------------------------------------------
> Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117
> View this thread: http://www.excelforum.com/showthread...hreadid=573763

--

Dave Peterson

[Mark K]

Thanks for that Dave, pointed me in the right direction.

Had to make a slight change to the numbers - discovered that there was an extra space at the end of the original cell data. Heres what I ended up with in my macro:

' Fix Text and Date

' Insert 2 'Helper' columns

Columns("C:D").Select
Selection.Insert Shift:=xlToRight

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = cLastRow To 1 Step -1

' Some rows don't have test/date so need to be sure not to work on them

If InStr(1, Cells(r, "A"), "Starts:") < 1 Then
If InStr(1, Cells(r, "A"), "Performances") < 1 Then

' Set the cells need to do the job

Set a = Cells(r, "D") ' Target for date
Set b = Cells(r, "B") ' Cell containing text/date
Set c = Cells(r, "C") ' Target for text

' Get the date

a.Value = DateValue(Mid(Right(b, 11), 1, 3) & Mid(Right(b, 8), 1, 3) & Mid(Right(b, 5), 1, 4))

' Get the text

c.Value = Left(b, Len(b) - 12)

End If
End If
Next r

' Delete the original column, no longer needed

Columns("B:B").Select
Selection.Delete Shift:=xlLeft

Works like a charm. Another success on a good day of learning VBA. Worked out how to use basic arrays, learnt about using offsets with ranges and now this. Very happy.

Thanks again for the input.