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

1. 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

2. 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. 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.

Wassim

4. 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)

5. 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. Re: Finding the 7th space in a string (xl 2003)

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

Posting Permissions

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