Function to return Character Position of Xth character within a string

[Andibevan]

Hi All,

I have a CSV data extract that I am trying to automatically seperate using a
formula -each string contains about 10 pieces of data each seperated by a
comma

I am trying to find / build a function that can return the character
position within the string of the Xth comma - I also need to be able to
specify whether it looks for the xth comma from the front or the back of the
string.

I am currently achieving this by using multiple Mid and SEARCH functions but
the formulas end up enormous and are hard to maintain.

Any help would be really appreciated

Ta

Andi

 

[Guest]

Sounds like all you want to do is parse each string on commas. Use the Split
function. It returns a string array (zero-based) parsed using the character
you select.

Dim MyData() As String
Dim strCSV As String
Dim i As Long
Dim OneItem As String

MyData = Split(strCSV, ",")

For i = 0 To UBound(MyData)
OneItem = MyData(i)
....do whatever...
Next i

 

[Andibevan]

Hi Charlie - Thanks for providing some advice but I don't quite understand
how your method could provide the character position of the Xth character?


Sounds like all you want to do is parse each string on commas. Use the
Split
function. It returns a string array (zero-based) parsed using the character
you select.

Dim MyData() As String
Dim strCSV As String
Dim i As Long
Dim OneItem As String

MyData = Split(strCSV, ",")

For i = 0 To UBound(MyData)
OneItem = MyData(i)
....do whatever...
Next i

 

[TT]

Hi,

If I understand you correctly you want to find the position of for
example the 4th comma in a string?
This function might do that:

Function iGetPosition(sInput As String, sSeparator As String, iNth As
Integer) As Integer
Dim iPos As Integer, iCnt As Integer

iPos = 0
For iCnt = 1 To iNth
iPos = InStr(iPos + 1, sInput, sSeparator)
If iPos = 0 Then
' bail out when the separator is not found
Exit For
End If
Next iCnt

iGetPosition = iPos
End Function

Sub Effe()
' we should see 18 as a result; let's cross fingers...
Debug.Print iGetPosition("aap,noot,mies,wim,zus,jet", ",", 4)
End Sub


With kind regards,
Ton Teuns

 

[Andibevan]

Thanks Ton - that does work as well.

As I had a bit of time to play with this one I have actually managed to
solve it myself by re-engineering the MyExtract function from

http://www.meadinkent.co.uk/xlextracttext.htm

Here is my code - I have also managed to get it to look from the front or
the back....

Function CountMyChar(strCSV As String, ItemNo As Integer, FrontOrBack As
String, _
MySeparator As String) As String

Dim i As Long
Dim OneItem As String
Dim n As Integer
Dim Var_CharCount As Integer
Dim Var_NumCharCount As Integer 'n'th item to find position of

Var_CharCount = 0 'current count of Item is 0

If UCase(FrontOrBack) = "F" Then
MySt = 1
MyFin = Len(strCSV)
MyStep = 1
Else
MySt = Len(strCSV)
MyFin = 1
MyStep = -1
End If

For n = MySt To MyFin Step MyStep
char = Mid(strCSV, n, 1)

If char = MySeparator Then
Var_NumCharCount = Var_NumCharCount + 1
End If

If Var_NumCharCount = ItemNo Then
Exit For
End If

Next n

CountMyChar = n

End Function


Hi,

If I understand you correctly you want to find the position of for
example the 4th comma in a string?
This function might do that:

Function iGetPosition(sInput As String, sSeparator As String, iNth As
Integer) As Integer
Dim iPos As Integer, iCnt As Integer

iPos = 0
For iCnt = 1 To iNth
iPos = InStr(iPos + 1, sInput, sSeparator)
If iPos = 0 Then
' bail out when the separator is not found
Exit For
End If
Next iCnt

iGetPosition = iPos
End Function

Sub Effe()
' we should see 18 as a result; let's cross fingers...
Debug.Print iGetPosition("aap,noot,mies,wim,zus,jet", ",", 4)
End Sub


With kind regards,
Ton Teuns