PDA

View Full Version : Solved: Parse Method



Nocturne
04-25-2008, 07:32 AM
I need some help on parsing. I am unfamiliar with it and need an answer in a short time. I have attached a spreadsheet with the info as is and how it should be. The parse method works but some of the items to be parse are different text lengths. If anyone can help I would very much appreciate it.

Thanks

Bob Phillips
04-25-2008, 07:58 AM
A13: =LEFT(A4,FIND(" ",A4)-1)
B13: =LEFT(SUBSTITUTE(A4,A13&" ",""),FIND(" ",SUBSTITUTE(A4,A13&" ",""))-1)
C13: =SUBSTITUTE(SUBSTITUTE(A4,A13&" ",""),B13&" ","")

and copy down

tstav
04-25-2008, 08:24 AM
With VBA:
Sub SplitThem()
Dim arr As Variant, i As Integer, newRow As Long, cel As Range
newRow = 13 'Supply the correct row
For Each cel In Range("A4:A7") 'Supply the correct range
arr = Split(cel.Value)
With Range("A" & newRow)
.Offset(0, 0).Value = arr(0)
.Offset(0, 1).Value = arr(1)
For i = 2 To UBound(arr)
.Offset(0, 2).Value = .Offset(0, 2).Value & arr(i) & " "
Next
.Offset(0, 2).Value = Left(.Offset(0, 2).Value, Len(.Offset(0, 2).Value) - 1)
End With
newRow = newRow + 1
Next
End Sub

tstav
04-25-2008, 08:33 AM
Or:
Sub SplitThem()
Dim arr As Variant, newRow As Long, cel As Range
newRow = 13 'Supply the correct row
For Each cel In Range("A4:A7") 'Supply the correct range
arr = Split(cel.Value)
With Range("A" & newRow)
.Offset(0, 0).Value = arr(0)
.Offset(0, 1).Value = arr(1)
.Offset(0, 2).Value = Right(cel, Len(cel) - Len(.Offset(0, 0).Value) - Len(.Offset(0, 1).Value) - 2)
End With
newRow = newRow + 1
Next
End Sub

tstav
04-25-2008, 08:36 AM
Or:
Sub SplitThem()
Dim newRow As Long, cel As Range
newRow = 13 'Supply the correct row
For Each cel In Range("A4:A7") 'Supply the correct range
With Range("A" & newRow)
.Offset(0, 0).Value = Split(cel.Value)(0)
.Offset(0, 1).Value = Split(cel.Value)(1)
.Offset(0, 2).Value = Right(cel, Len(cel) - Len(.Offset(0, 0).Value) - Len(.Offset(0, 1).Value) - 2)
End With
newRow = newRow + 1
Next
End Sub

Nocturne
04-30-2008, 11:18 AM
Thanks to all that replied.