# Find where the 2nd, 3rd, 4th space is?

• I have a text string with four spaces in it. I can find the location of the first space easy enough.

=FIND(" ",B1) (returns where the first space is)

Second space is a little trickier, but I figured out a way...

=FIND(" ",B1)+FIND(" ",RIGHT(B1,LEN(B1)-FIND(" ",B1)))

I'm hoping there's an easier way that I don't know of... because taking this to the 3rd & 4th space is gonna be really ugly.

Any ideas?

• Jas,

for the example below text string is in A4

B4 =SEARCH(" ",\$A4,1)
C4 onwards =SEARCH(" ",\$A4,B4)+B4
The formula in B4 can be dragged across columns until you have enough columns for spaces.

This could be modified a little to eliminate error values appearing by using an IF statement.

Hope that helps
Weasel

• That's a similar mechanism to what I'm using... the problem is I need to be able to start to finish return what place the 4th space is in without using multiple cells.

So far, I'm up to this formula:

=FIND(" ",B1,FIND(" ",B1,FIND(" ",B1,FIND(" ",B1)+1)+1)+1)

I was just hoping there was something a little cleaner... as I'm using nested versions of that formula combined with LEFTs, RIGHTs, and LENs to pull out the strings that are delineated by those spaces.

What's fun is my file actually uses more than one space between fields, so it actually has TRIMs mixed throughout the whole thing. My formulae are getting really LONG and confusing. :o I was hoping there was a shortcut I didn't know about.

Ah, well, I'm slowly getting it to work!

Example (pulling third text string):

=RIGHT(LEFT(TRIM(B1),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1))+1)+1)),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1))+1)+1)-(FIND(" ",TRIM(B1),FIND(" ",TRIM(B1))+1)))

• These are a little shorter ... I think.

1st space:

=FIND(" ",TRIM(\$B1))

2nd space:

=FIND(" ",TRIM(\$B1),FIND(" ",TRIM(\$B1))+1)

3rd:

=FIND(" ",TRIM(\$B1),FIND(" ",TRIM(\$B1),FIND(" ",TRIM(\$B1))+1)+1)

4th:

=FIND(" ",TRIM(\$B1),FIND(" ",TRIM(\$B1),FIND(" ",TRIM(\$B1),FIND(" ",TRIM(\$B1))+1)+1)+1)

• Hi All,

I've only tested this briefly so it may need some tweaking ;;) . The UDF:

Either call from a sub like this:

Code
``````Sub Test()
Const strMain As String = "A string with four spaces"
Const strFind As String = " "
Const lPos As Long = 4
MsgBox "Item " & lPos & " of the string " & "'" & strFind & "'" & _
" within the string " & "'" & strMain & "'" & " is at position : " _
& FindPosition(strMain, strFind, lPos)
End Sub``````

or within a spreadsheet like this:

EDIT: Removed corrupt pic

HTH

• Trying again with the pic ;;)

EDIT : Good catch re the spaces Andy (see below)

• Hi Jas,

Here is another alternative.
It uses the SUBSTITUTE function to place a characters, one that is not in the original text, at the Nth position.
The find can then be used to locate the single character.

For the sake of displaying this via HTML Maker I have had to use a # instead of a space.
Also the CHAR(126) ~ would normally be the CHAR(127) , although any character not in the original text will do.

Cheers
Andy

[h4]Cheers
Andy
[/h4]

• Those are some nice solutions! Thanks to all... I'm going to hit the books on UDFs to see if I can learn more about how this works so I can do more on my own... I'm thinking a UDF that pulls the 1st, 2nd, 3rd, 4th, or 5th string would be cool. Thanks for the nudge in the right direction! And Andy - your solution is immediately useable without me hitting the books hard. Gracias! :wink1:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!