Parse (TRIM) File Name into seperate columns

[DizzyD]

I have an .exe that reads a folder full of files and then
puts the complete file into column A. I would like to
parse (trim) the file name into seperate columns.
File name exampleNumber of characters always same)
ABC-040901-1015-incoming.dat

I would like "ABC" in column A
"040901-1015" in column B

disregard remaining characters..

I have been trying TRIM and Left but can't get it to work.
Any help greatly appreciated!
Thanks...

 

[Soo Cheon Jheong]

Diana,


(Data)
---------------A---------------------B---------C--------
1 File name
2 ABC-040901-1015-incoming1.dat
3 ABD-040902-1016-incoming2.dat
4 ABE-040903-1017-incoming3.dat
5 .
6 .
7 .
---------------A---------------------B---------C--------


Macro A
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub Test_1()

Dim CL As Range
Dim Pos1 As Integer
Dim Pos2 As Integer
Dim R As Long
Dim T As String

Application.ScreenUpdating = False

R = Cells(Rows.Count, 1).End(xlUp).Row
If R = 1 Then GoTo e:

With Range("B2:C" & R)
.ClearContents
.Rows(0).Value = Array("File name", "Code")
End With

For Each CL In Range("A2:A" & R)

Pos1 = InStr(1, CL.Text, "-")
Pos2 = InStrRev(CL.Text, "-", -1)

CL(1, 2).Value = Left(CL.Text, Pos1 - 1)
CL(1, 3).Value = Mid(CL.Text, Pos1 + 1, Pos2 - Pos1 - 1)

Next

e:
Application.ScreenUpdating = True

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -


Macro B
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub Test_2()

Dim R As Long

Application.ScreenUpdating = False

R = Cells(Rows.Count, 1).End(xlUp).Row
If R = 1 Then GoTo e:

With Range("B2:C" & R)
.ClearContents
.Rows(0).Value = Array("File name", "Code")
.Columns(1).Formula = "=LEFT(A2,FIND(""-"",A2)-1)"
.Columns(2).Formula = "=MID(A2,FIND(""-"",A2)+1," & _
"FIND(""-"",A2,FIND(""-"",A2," & _
"FIND(""-"",A2)+1)+1)-1-FIND(""-"",A2))"
.Value = .Value
End With

e:
Application.ScreenUpdating = True

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Chip Pearson]

Try something like the following:

Dim S As String
Dim Pos As Integer
Dim Pos2 As Integer
S = ActiveCell.Text
Pos = InStr(1, S, "-")
ActiveCell.EntireRow.Cells(1, "B").Value = _
Left(S, Pos - 1)
Pos2 = InStrRev(S, "-")
ActiveCell.EntireRow.Cells(1, "C").Value = _
Mid(S, Pos + 1, Pos2 - Pos - 1)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com