Find positions in a string

[lins0340]

I would like to retrieve the exact positions of point in a string. Example:

"12.3.23.5.10." The result should be:

point 1 : 3
point 2 : 5
point 3 : 8
point 4 : 10
point 5 : 13

Can anybody tell me how to do that?

 

[njimack]

Excel Workbook
	A	B
1	12.3.23.5.10.	3
2		5
3		8
4		10
5		13
Sheet1

 

[Ron Coderre]

With
A1: containing your sample value: 12.3.23.5.10.

and Col_D cells containing the instance to locate
D1: 1
D2: 2
D3: 3
D4: 4
D5: 5

This regular formula returns the position of the decimal point referenced in Col_D

E1: =FIND("|",SUBSTITUTE($A$1,".","|",D1))

Copy that formula down through E5 to see the other positions.

These are the E1:E5 results:
3
5
8
10
13

Is that something you can work with?

 

[Warship]

With VBA:

Sub GetPointPositions()
    Dim PointData As String
    Dim PointCount As Integer
    Dim PointPositions As String
    Dim LastPoint As Integer
    Dim ThisPoint As Integer
    Dim i As Integer
    
    PointData = Range("A1")
    PointCount = Len(PointData) - Len(Replace(PointData, ".", ""))
    For i = 1 To PointCount
        ThisPoint = InStr(LastPoint + 1, PointData, ".")
        PointPositions = PointPositions & ThisPoint & ","
        LastPoint = ThisPoint
    Next i
    PointPositions = Left(PointPositions, Len(PointPositions) - 1)
    
    'There are various ways to extract individual positions
    Range("A2:A6").ClearContents
    For i = 1 To PointCount
        Range("A" & i + 2) = "point " & i & " : " & Split(PointPositions, ",")(i - 1)
    Next i
End Sub