Separating text and dates.

[Mark K]

Hi all.

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

My current solution is to first search and replace the space in th
text with a hyphen then do the text to columns. So far I have the macr
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'
remember exactly how I did it) but then I found it was reversing the da
and the month when separated.

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

TI

 

[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]

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").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.