Hi!

The [ character is used as a "marker". It marks the spot of the last
instance of the space character then instead of searching for a space, the
formula searches for [ since it is now a unique character as opposed to the
space character.

Not real clear is it?

Try this:

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

Counts the number of spaces in the string = 2

SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

Substitutes the 2nd instance of space with [ so that the string looks like
this to the formula:

3700 Chestnut[Pasadena

FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

Returns the position of [ in the string = 14. This is the value used in the
LEFT function as to how many characters to return. Also, I goofed up in that
I should have subtracted 1 from that value because we only want characters
to the right of the 14th position and not including the 14th position, so:

FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1


=LEFT(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

Reduced to what it actually does:

=LEFT(A1,13)

Return 13 characters starting from the left in the string of cell A1.

Biff

"gbeard" wrote in message
m...
Biff,
In the formula you wrote:

=LEFT(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

What does the "[" do?

--
Gary Beard