Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse (TRIM) File Name into seperate columns
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 example:(Number 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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse (TRIM) File Name into seperate columns
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 _ _ ^ąŻ^ -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse (TRIM) File Name into seperate columns
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 "DizzyD" wrote in message ... 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 example:(Number 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I parse one column into different columns? | Excel Discussion (Misc queries) | |||
How do I seperate a full name field out into three seperate columns? | Excel Worksheet Functions | |||
seperate first 2 lines of column in seperate columns in same row | Excel Discussion (Misc queries) | |||
Parse Data to Various Columns | Excel Discussion (Misc queries) | |||
How do I parse columns? | Excel Worksheet Functions |