 # Removal of spaces

• Hi

If anyone can help with this tough one, I'd be delighted!

Assume cell A1 contains a set of words. If cell B1 contained the following formula, the contents of cell A1 will be shown in B1, minus the last word.

Code
``=IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,LOOKUP(32768,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))),A1)``

However, the space or gap before the word that has been removed will be present in cell B1.

eg. If A1 = "how are you", then B1 will = "how are ".

I need this space to be removed too.
ie. B1 = "how are"

Any ideas?

Hope that makes sense!

Many thanks
Johno

• Re: Removal of spaces

Never used 'indirect' formula before, but surely it would simply be 'Left(....)-1'

• Re: Removal of spaces

Johno,

Taking one off the lenght should work.

Code
``=IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,LOOKUP(32768,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))-1),A1)``

Ross

• Re: Removal of spaces

Thanks chaps!
:saychees:

## Participate now!

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