Change delimiter position based off of Last Name

M

Matt P

Alright, here we go... I have no idea where to start because I am not
to familiar with excel vba programming. But let me try and explain
what the situation is to the best of my ability. I have a page of
addresses that was scanned and converted over to word with some image
to text software. Then I got it into a text file and finally a csv
file. Its in this format most of the time:
Examples-
Row 1 - ABEL, Mary-Fund Mutual Grp, Tec Clms
Row 2 - Expert, PO Box 1621, Houston, TX 77054
Row 3 - P555-621-7474, F555-218-1998
Row 4 - ARLINGTON, Kyle-Full Ins Grp, Clms
Row 5 - Expert, 2800 Post Oak Blvd, Houston, TX 77056
Row 6 - P664-621-7474, F665-218-1998

(the number of rows in between each address sometimes varies)

and I need it in this format:
Row 1 - ABEL, Mary-Fund Mutual Grp, Tec Clms Expert, PO Box 1621,
Houston, TX 77054, P555-621-7474, F555-218-1998
Row 2 - ARLINGTON, Kyle-Full Ins Grp, Clms Expert, PO Box 3434,
Houston, TX 77054 P664-621-7474, F665-218-1998

All one line and 8 columns. I figure it would be easiest to write a
script that loops through the csv file and searches for a text that
only has letter characters and all caps in the front of the row...
which would be the Last Name field also which is always in the front.
Then in between each it removes the "returns". I don't know if I was
clear enough or if someone needs further clarification by all means...
I am absolutely stuck and I really would appreciate any help or
guidance on the situation! I have looked at other examples of looping
a csv file... it seems pretty basic, but this to me is a little mind
boggling.

Thanks for any help!!

-Matt P
 
P

Patrick Molloy

is every "line" spread over two rows? If so, then step 1 would be to
concatenate the 2nd rows to the 1st row then delete the 2nd row

Hit Alt +F11 to open the development environment.
Then on the menu hit Insert / module

this opens a standard code modue
paste this:

Sub combine()
Dim rw As Long
For rw = Range("A1").End(xlDown) To 2 Step -2
Cells(rw - 1, 1) = Cells(rw - 1, 1) & " " & Cells(rw, 1)
Rows(rw).Delete
Next
End Sub

the code finds the last row. the data from this is added to the data from
the row above, then the row deleted. this is repeated for every row

all being well, you now have a column of data comma separated. Now you can
use the worksheet menu Data then Text To Columns to tablulate the data
 
J

Jacob Skaria

Matt,

Try the below..You dont need to open the CSV file. Open a workbook. Change
the file name...mentioned in the code and try....

Sub ReadCSV()

Dim intFile As Integer
Dim strData As String
Dim strTemp As String
Dim arrData As Variant
Dim lngRow As Long

intFile = FreeFile
Open "c:\1.csv" For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strData
arrData = Split(strData, ",")
If IsNumeric(Left(arrData(0), 1)) = False And _
IsNumeric(Right(arrData(0), 1)) = False And _
UCase(arrData(0)) = arrData(0) Then
If strTemp <> "" Then Range("A" & lngRow) = strTemp
lngRow = lngRow + 1
strTemp = Trim(strData)
Else
strTemp = strTemp & "," & Trim(strData)
End If
Loop
Close #intFile
Range("A" & lngRow) = strTemp

End Sub



If this post helps click Yes
 
M

Matt P

Matt,

Try the below..You dont need to open the CSV file. Open a workbook. Change
the file name...mentioned in the code and try....

Sub ReadCSV()

Dim intFile As Integer
Dim strData As String
Dim strTemp As String
Dim arrData As Variant
Dim lngRow As Long

intFile = FreeFile
Open "c:\1.csv" For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strData
arrData = Split(strData, ",")
If IsNumeric(Left(arrData(0), 1)) = False And _
IsNumeric(Right(arrData(0), 1)) = False And _
UCase(arrData(0)) = arrData(0) Then
If strTemp <> "" Then Range("A" & lngRow) = strTemp
lngRow = lngRow + 1
strTemp = Trim(strData)
Else
strTemp = strTemp & "," & Trim(strData)
End If
Loop
Close #intFile
Range("A" & lngRow) = strTemp

End Sub

If this post helps click Yes
Click to expand...

Yes, Wow thank you Jacob Skaria that worked perfect!
 
Top