FIND query

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

I have this record in a cell

A1 = a big tree

I need to find where all " "'s are.

I used =find(a1," ") to find the first space
now i need to find the second, any idea's?
 
Hi
depending on the number of spaces in your string this could getting
complicated(what are you trying to achieve - there may be better
alternatives like Dat - Text to columns to split your data into
multiple columns)

For your example you may use the following formula to get the second
space position:
=FIND(" ",A1,FIND(" ",A1)+1)
 
Just use the SUBSTITUTE function and the last optional parameter for the
instance number to replace that character with something like "¬" which wouldn't
normally appear. Then just use FIND to get the position.

=FIND("¬",SUBSTITUTE(A1," ","¬",2))
 
And of course you can calculate the actual number of spaces using:-

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
 
Back
Top