.CSV file problems

[Mel Smith]

Hello folks,

I'm trying to use data imported from a .csv file but I have encountered a couple of problems in how it has interpreted/displayed some information.

Please see below the screen shot from the original data:

and see also the way the .csv file has displayed it:

20210926-103551-bowling-st.csv
	M	N
1	Best Bowling in an Innings	Best Bowling in a Match
2	Mar-35	Apr-71
3	Mar-77	Apr-77
4	0-6	0-6
5	Mar-26	Mar-87
6	Feb-40	3-103
7	0-21	0-21
8	Mar-39	6-139
20210926-103551-bowling-st

Is there any way to overcome this? These examples are just a small selection of a large file.

Thanks,

Mel

 

[GWteB]

Using the import wizard (Ribbon > Data tab > From Text), for each column changing its data format from General to Text should give you the expected result ...

 

[cmowla]

Insert a new code module ( Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com )
And run this code to fix it (click the play button or press F5 with the code window open). (But of course, ideally, you want to make sure that your .csv is formatted as text, not dates . . . to prevent this from happening again.

Sub Fix_Data()

ActiveSheet.UsedRange.NumberFormat = "@"

For Each cell In ActiveSheet.UsedRange
    cell.Value = Month(ActiveCell) & "-" & Right(Year(ActiveCell), 2)
Next cell

End Sub

 

[Mel Smith]

Thank you, both, for your helpful replies. I've gone with "cmowla's" solution.

Mel

 

[cmowla]

I just realize that the code I wrote will wreck the data! I'm fixing it! (Sorry!)

 

[Mel Smith]

I tried it on dummy data and decided to then go with the previous solution. No harm done…

Mel

 

[cmowla]

Just for the sake of it, I wrote code which looks like it actually fixes the data. ("ActiveCell" in my previous code, in addition to it being fundamentally wrong? Clearly I was careless!)

Sub Fix_Data()

For Each cell In ActiveSheet.UsedRange
    If MyIsDate(cell.Value) = True Then
        cell.NumberFormat = "@"
        cell.Value = Month(cell.Value) & "-" & Right(Year(cell.Value), 2)
    Else
        cell.NumberFormat = "@"
    End If
Next cell

End Sub

Sub Test__MyIsDate()
MsgBox MyIsDate(ActiveCell.Value)
End Sub
Function MyIsDate(str As String)
MyIsDate = InStr(Format(str, "@"), "/") > 0
End Function

 

[Mel Smith]

Thank you for this. I’ll try it out tomorrow and see which method is more suited (and fewer steps!).

Mel

 

[cmowla]

Thank you for this. I’ll try it out tomorrow and see which method is more suited (and fewer steps!).

Mel

It doesn't work perfectly for me because, for some reason, I can't format dates as MonthName-Day (Mar-26 = March 26th) from the number format menu. I just have MonthName-Year (Mar-21 = March 2021).

But if your version of Excel is set to another country's date format (not US English) indeed has that formatting (it must, given on the comparison with original data and what shows up in your spreadsheet), then it should work.