Stop converting numbers to date while importing text file using VBA in excel

Question

I have to import text files to excel. These text files are huge and the data are in rows which I have to transpose to columns for which I have a simple code as given below.

Sub extraction()

Set tr = Selection
j = 2
For i = 1 To tr.Rows.Count

    If Left(tr.Cells(i, 1), 6) = "(DATE)" Then
    j = j + 1
        Cells(j, 5) = Mid(tr.Cells(i, 1), 7, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 20) = "(SEA/WIND DIRECTION)" Then
        Cells(j, 6) = Mid(tr.Cells(i, 1), 21, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 16) = "(SEA/WIND POWER)" Then
        Cells(j, 7) = Mid(tr.Cells(i, 1), 17, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 7) = "(SPEED)" Then
        Cells(j, 8) = Mid(tr.Cells(i, 1), 8, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 7) = "(MILES)" Then
        Cells(j, 9) = Mid(tr.Cells(i, 1), 8, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 10) = "(FUEL AUX)" Then
        Cells(j, 10) = Mid(tr.Cells(i, 1), 11, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 21) = "(TOTAL STEAMING TIME)" Then
        Cells(j, 11) = Mid(tr.Cells(i, 1), 22, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 5) = "(RPM)" Then
        Cells(j, 12) = Mid(tr.Cells(i, 1), 6, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 6) = "(SLIP)" Then
        Cells(j, 13) = Mid(tr.Cells(i, 1), 7, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 7) = "(POWER)" Then
        Cells(j, 14) = Mid(tr.Cells(i, 1), 8, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 14) = "(DISPLACEMENT)" Then
        Cells(j, 15) = Mid(tr.Cells(i, 1), 15, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 13) = "(FUEL M/E HS)" Then
        Cells(j, 16) = Mid(tr.Cells(i, 1), 14, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 13) = "(FUEL M/E LS)" Then
        Cells(j, 17) = Mid(tr.Cells(i, 1), 14, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 9) = "(OIL CYL)" Then
        Cells(j, 18) = Mid(tr.Cells(i, 1), 10, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 15) = "(STEAMING TIME)" Then
        Cells(j, 19) = Mid(tr.Cells(i, 1), 16, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 22) = "(STEAMING TIME M/E HS)" Then
        Cells(j, 20) = Mid(tr.Cells(i, 1), 23, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 22) = "(STEAMING TIME M/E LS)" Then
        Cells(j, 21) = Mid(tr.Cells(i, 1), 23, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 10) = "(FUEL M/E)" Then
        Cells(j, 22) = Mid(tr.Cells(i, 1), 11, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 11) = "(ECO SPEED)" Then
        Cells(j, 23) = Mid(tr.Cells(i, 1), 12, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 11) = "(MILES ECO)" Then
        Cells(j, 24) = Mid(tr.Cells(i, 1), 12, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 8) = "(BHP KW)" Then
        Cells(j, 25) = Mid(tr.Cells(i, 1), 9, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 10) = "(SEA/WIND)" Then
        Cells(j, 6) = Mid(tr.Cells(i, 1), 11, Len(tr.Cells(i, 1)))
        ElseIf Left(tr.Cells(i, 1), 16) = "(SEA WIND POWER)" Then
        Cells(j, 7) = Mid(tr.Cells(i, 1), 17, Len(tr.Cells(i, 1)))


       End If


     Next i

  End Sub

Now, the problem is, in the SEA/WIND POWER column, there are data like 3/2 which when importing automatically gets converted to the date format (02-Mar).

What I want is, either the original text(say 3/2) or the greater number. Being a newbie in VBA, I just can't get to solve the issue.

Any help will be greatly appreciated. Thank you :)

user2140173 · Accepted Answer · 2013-08-13 11:40:20Z

1

Run below Sub before importing txt files

Sub Prep()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        ws.Cells.NumberFormat = "@"
    Next
End Sub

this sub iterates through all sheets and formats all cells to text, so imported values will appear in original format.

answered Aug 13, 2013 at 11:40

user2140173

Sign up to request clarification or add additional context in comments.

Comments

tomelin5 · Accepted Answer · 2014-02-17 15:49:27Z

0

Another dirty way to get this is add an apostrophe to it. For example:

<!-- language: VB -->
    Cells(j, 5) = "'" & Mid(tr.Cells(i, 1), 7, Len(tr.Cells(i, 1)))

This will import '3/2

edited Feb 17, 2014 at 15:49

answered Feb 17, 2014 at 13:29

tomelin5

551 silver badge7 bronze badges

Collectives™ on Stack Overflow

Stop converting numbers to date while importing text file using VBA in excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related