Results 1 to 3 of 3
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Need Formula for Points (Excel 2002 )

    Does this function do what you want?

    <pre>Function ListPositions(sString As String, sChar As String)
    Dim i As Integer
    Dim iCount As Integer
    Dim iStart As Integer
    Dim iArray() As Integer
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction
    iCount = Len(AWF.Substitute(sString, sChar, ""))
    iCount = (Len(sString) - iCount) / Len(sChar)
    ReDim iArray(1 To iCount)
    iStart = 1
    For i = 1 To iCount
    iArray(i) = InStr(iStart, sString, sChar)
    iStart = iArray(i) + Len(sChar)
    ListPositions = iArray
    Set AWF = Nothing
    End Function</pre>

    Call it Like this in a macro:
    <pre>Sub Test()
    Dim a
    a = ListPositions("I Love lovely Large Watermellons", "L")
    End Sub</pre>

    or use:
    <pre>=listpositions("I Love lovely Large Watermellons","L")</pre>

    in a cell. Note that it is an array function (you indicated that you wanted an array) so it must be entered with ctrl-shift-enter. It wil put each number into its own cell horizontally.

    You can also use:
    <pre>=Index(listpositions("I Love lovely Large Watermellons","L"),1) </pre>

    to get the first postition ( which is 3) and :

    <pre>=Index(listpositions("I Love lovely Large Watermellons","L"),2) </pre>

    to get the 2nd postition ( which is 15), etc
    [Note you will get a #Ref error if you ask for a position greater than the number of char to search]

    The code is written that the item to be searched may be more than 1 character...

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    New York, New York, Lebanon
    Thanked 1 Time in 1 Post

    Need Formula for positions in string (Excel 2002 )

    Subject edited by HansV

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Hans

    How do you write an array formula to give you all the positions of a letter in a string:

    Say you have: "I Love lovely Large Watermellons"

    What formula will return the positions of all the Ls and ls in an array format? {3,15} for Ls

    Thank a million <img src=/S/money.gif border=0 alt=money width=17 height=15>

    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Super Moderator
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 405 Times in 334 Posts

    Re: Need Formula for Points (Excel 2002 )

    <P ID="edit" class=small>(Edited by macropod on 30-Apr-06 22:35. Oops - these formulae just give counts, not positions (sigh))</P>Hi Wassim,

    The array formula:
    =SUM(1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="n") )
    will return the number of occurrences of the "n" string (ie "n" can be more than one character) in cell A1. You can replace "n" with the reference to a cell containing "n".

    You could also use:
    as a standard formula.

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts