See if you can adapt this:
'-----------------------------------------------------
Option Explicit
Public Const ForReading = 1, ForWriting = 2, ForAppending = 3
Sub ReadData()
Dim fso, ft, Str, CSVFile
Dim R
ThisWorkbook.Activate
Sheets("Sheet1").Select
CSVFile = "C:\temp\vba\TestData.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.fileexists(CSVFile)) Then
Sheets("Sheet1").Range("A1:Z65000").ClearContents
R = 0
Set ft = fso.OpenTextFile(CSVFile, ForReading)
If (Err.Number <> 0) Then
MsgBox "Could not open " & CSVFile
Exit Sub
End If
Do While Not ft.atendofstream
Str = UCase(ft.readline)
If (InStr(1, Str, " 12 ") > 0) Then
Str = Left(Str, InStr(1, Str, " 12 "))
End If
R = R + 1
Sheets("Sheet1").Cells(R, 1).Value = Str
Loop
ft.Close
End If
End Sub
'---------------------------------------------------------------
Paul
________________________________
From: CAN <cdelano...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tue, March 1, 2011 12:39:33 PM
Subject: $$Excel-Macros$$ Delete extra information from a cell
I have a .CSV file that contains all the data in column A. Each row
has different information.
I am looking for macro that will review the data on each row and
whenever find the number "12" it will delete it along with all the
information after.
For example see data below
1 MF2-001 B Y MILAGROS ALVARADO 12 $140.00 01/01/11 $25.00 14 DAYS
2 MF2-002 B Y OLIVER B. & INGRID Y. URQUIETA 12 $140.00 01/01/11
$25.00 14 DAYS
3 MF2-003 B Y LUCIA G. FERNANDEZ 12 $140.00 01/01/11 $25.00 14 DAYS
4 MF2-004 B Y JOHNNY BARAKAT 12 $140.00 01/01/11 $25.00 14 DAYS
The data on each row is only in column A. In row 1 I need a macro
that will delete all the words or caracters after the word "Alvarado".
In row 2 to delete all the words after "Urquieta"
In row 3 to delete all the words after "Fernandez", etc, etc.
In other words to identify in each colum "space+ 12" and delete
anything that begins with 12 but leaving intact everything before.
After the macro runs, the data must look like this.
1 MF2-001 B Y MILAGROS ALVARADO
2 MF2-002 B Y OLIVER B. & INGRID Y. URQUIETA
3 MF2-003 B Y LUCIA G. FERNANDEZ
4 MF2-004 B Y JOHNNY BARAKAT
The macro needs to loop until the last row.
I will appreciate any help with this. Thank you in advance.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
