Left worksheet function

[Guest]

hello
I'm trying to =the first word in another cell and copy the formula down. The
first word has different amount of characters in each cell so I cannot do
Left,4 for example as in some of the cells in has 5 characters.

Is there anyway i can do a formula that reads the first word, or the last
word only?

Many thanks

 

[MartinW]

Hi Fiona,

Little bit short on detail.
This will find anything to the left of the first space.
=LEFT(A1,FIND(" ",A1)-1)

Post examples of your data if you need more than that.

HTH
Martin

 

[Frederik]

Hello Fiona

Use this formula, supposing the text is in cell D5:

=LEFT(D5,SEARCH(" ",D5,1)-1)

greetings

 

[Guest]

Hi Martin
Thank you, that's exactly what I need. I'm trying to seperate the name of
someone so I have the forename in one box and the surname in another.

So you know how I can get the surname out, this is the 2nd and final word, i
tried to change your formula from left to right but it didn't work.

Thank you

 

[MartinW]

HI Fiona,

I think this is what you need.
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

HTH
Martin

 

[Bob Phillips]

=MID(A1,FIND(" ",A1)+1,99)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Martin, that's exactly it!

HI Fiona,

I think this is what you need.
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

HTH
Martin

 

[MartinW]

You're welcome Fiona. Don't forget to check out the other responses.
Bob's formula is shorter, therefore, it is better for large spreadsheets.
Fredericks use of Search instead of Find is also better in some
circumstances.

Basically it comes down to there are no real rules for this sort of
manipulation.
Just a whole pile of techniques that need to be adjusted for each situation
as it arises.

Regards
Martin