Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Finding the 7th space in a string (xl 2003)

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

    I am trying to find the 7th space in a string to parse.

    The data is coming from a main frame application, and all I could find is that 7th space as a land mark.

    Any help would be greatly appreciated.

    Wassim
    <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>

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the 7th space in a string (xl 2003)

    Here is a VBA solution. I don't know of a way to do it without VBA

    <pre>Option Explicit

    Function get7(myCell As String) As Integer
    Dim i As Integer, spaceCount As Integer
    For i = 1 To Len(myCell)
    If Mid(myCell, i, 1) = " " Then
    spaceCount = spaceCount + 1
    If spaceCount = 7 Then Exit For
    End If
    Next
    get7 = i

    End Function
    </pre>


  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Finding the 7th space in a string (xl 2003)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> mbarron

    Yes I know you could do it in VBA, your example is excellet. But I was hoping to have a worksheet function to be able to use it on a worksheet to parse the string automatically.

    Maybe I'll go sleep and wake up tomorrow with a solution made up of all the right nested worksheet functions.

    Thanks for your reply <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Wassim
    <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>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the 7th space in a string (xl 2003)

    Well, this seems to give the position of the 7nth space:

    =SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1)+1)+1)+1)+1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding the 7th space in a string (xl 2003)

    In addition to Jan's formula you can use the shorter:
    <pre>=FIND(CHAR(1),SUBSTITUTE(A4," ",CHAR(1),7))</pre>


    It substitutes the 7th space with a char 1 and then finds this [I presume the likelihood of finding a char(1) is slim]

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the 7th space in a string (xl 2003)

    Why can't mbarron's function be used on a worksheet?
    Legare Coleman

Posting Permissions

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