I have a macro to cleanup and parse output from another program. The
macro deletes unneeded rows, then uses text-to-columns to partially
parse the information in each row.


For i = Selection.Rows.Count To 1 Step -1
If Not Selection.Cells(i, 1).Value Like "*=*" Then _
Selection.Cells(i, 1).Delete Shift:=xlUp
Next i
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.TextToColumns DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=True, Other:=False


I then finish cleaning up the output by manually applying additional
text-to-columns and find/replace functions. I would like to improve my
macro to avoid this manual clean up.

Here's an example of the lines I need to parse:

Distance = 7.22367.

Distance = 7.20009; dx= 0.115373, dy= 0.104041, dz= 7.19841.

Edge length = 5.28946

Surface area = 1.48977

I would like to parse the numbers from everything else, so that
numeric values are in cells by themselves. I don't want to throw away
the other characters (letters, spaces, etc) I just don't want them
mixed into cells with the numbers.

The main problem I have is with the rows with trailing periods. If I
could get rid of these my current code would work fine.

Any help would be greatly appreciated.

Dave Parker

Parse on
Range(ActiveCell, ActiveCell.End(xlDown)).TextToColumns _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, _
Comma:=True, Space:=False, Other:=True, OtherChar:="="


Maybe you need to trim the trailing dots before parsing.
so then..
Sub ff()

Dim cell As Range
With Range(ActiveCell, ActiveCell.End(xlDown))
For Each cell In .Cells
If Right(cell, 1) = "." Then cell = Left(cell, Len(cell) - 1)
Next


.TextToColumns _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, _
Comma:=True, Space:=False, Other:=True, OtherChar:="="
End With

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dave wrote :

I have a macro to cleanup and parse output from another program. The
macro deletes unneeded rows, then uses text-to-columns to partially
parse the information in each row.


For i = Selection.Rows.Count To 1 Step -1
If Not Selection.Cells(i, 1).Value Like "*=*" Then _
Selection.Cells(i, 1).Delete Shift:=xlUp
Next i
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.TextToColumns DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=True, Other:=False


I then finish cleaning up the output by manually applying additional
text-to-columns and find/replace functions. I would like to improve my
macro to avoid this manual clean up.

Here's an example of the lines I need to parse:

Distance = 7.22367.
Distance = 7.20009; dx= 0.115373, dy= 0.104041, dz= 7.19841.
Edge length = 5.28946
Surface area = 1.48977

I would like to parse the numbers from everything else, so that
numeric values are in cells by themselves. I don't want to throw away
the other characters (letters, spaces, etc) I just don't want them
mixed into cells with the numbers.

The main problem I have is with the rows with trailing periods. If I
could get rid of these my current code would work fine.

Any help would be greatly appreciated.

Dave Parker