parsing out data using VBA and functions

[jojones]

Hi

I have some data which I need to parse out as follows:

the data will be something like this

0001 Jo Jones
0002 Water Margin

I need to put 0001 in Column A and the rest(minus the space at the start) into Column B.

Normally I would either use Text to Columns, or if more than one instance of a " ", then user the left() and right() functions combined with the find()

ie

left(A1,find(" ",A1)-1) = 0001
right(A1,(len(A1)-find(" ",A1)-1)) = Jo Jones

I need to do the exact same thing using VBA, but VBA does not recognise the Find() function. Can anyone help me and suggest an alternative without having to write my own function.

I though there was some sort of substr (sub string) function in VBA...

thanks
Jo

 

[oharab]

The vba replacement for find() is instr().

As an alternative, you could use these functions:

Function CountCSWords(ByVal s, Delimeter As String) As Integer
'Counts the words in a string that are separated by delimeter.
Dim WC As Integer, pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
pos = InStr(s, Delimeter)
Do While pos > 0
WC = WC + 1
pos = InStr(pos + 1, s, Delimeter)
Loop
CountCSWords = WC
End Function

Function GetCSWord(ByVal s, Indx As Integer, Delimeter As String)
'Returns the nth word in a specific field.
Dim WC As Integer, Count As Integer
Dim SPos As Integer, EPos As Integer

WC = CountCSWords(s, Delimeter)
If Indx < 1 Or Indx > WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, Delimeter) + 1
Next Count
EPos = InStr(SPos, s, Delimeter) - 1
If EPos <= 0 Then EPos = Len(s)
GetCSWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function


HTH

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[ttmac]

The following sub routine will achieve the same result:-

Option Explicit

Sub Splitter()

Dim mypos As String
Dim myrange As Range
Dim Cell As Object

Set myrange = Range(&quot;a1:a6&quot

For Each Cell In myrange
If Not (IsNull(Cell.Value) Or IsEmpty(Cell.Value)) Then
mypos = InStr(Cell.Value, &quot; &quot
Cell.Offset(0, 1).Value = Left(Cell.Value, (mypos - 1))
Cell.Offset(0, 2).Value = Mid(Cell.Value, (mypos + 1), Len(Cell.Value))
End If
Next Cell
End Sub

You should change the values (&quot;a1:a6&quot in the line

Set myrange = Range(&quot;a1:a6&quot

to accomodate your range

Tom