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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.