Deleting text from cell after a space?

[excel-with-excel]

Hi, I have a Database which I need only one contact name for each company.
Unfortunately, there is up to 5 different names in each cell. Eg. "Smith Jones Walton Davies Wilson"

Could anyone help me with a formula that lets me just keep the first name in the cell… "Smith"

Thanks guys!

[sweep]

Hi,

Try this:

=LEFT(A1,FIND(" ",A1)-1)

[excel-with-excel]

Hiya Sweep, thanks, I have already tried this one… it didn't quite fit into my database.

Could you perhaps talk me through this Formula please? So that I can change the cell numbers to my database! If that makes sense?

[sweep]

Sure.

LEFT(A1,5) will return the 5 leftmost characters in cell A1, so if A1 contains "abcdefghij", it would return "abcde"

FIND(" ",A1) will return the first occurrence of a space in cell A1. If A1 contains "abcde fghi", it would return 6.

[excel-with-excel]

Hiya Sweep, thanks for this, but this makes it complicated for when I save this as a .csv for import into our Customer Management software.

Is there anyway I can be left with just the first surname without using a formula? Maybe a Macro for going into each cell individually, to remove the remaining names after the first one in each cell ?

(If this makes sense)

[sweep]

You could,

Use the formula,

Select all the cells that contain the formula,

Do Edit > Copy

then Edit> Paste Special > Values

If you must use code, this will get you started:

Please Login or Register  to view this content.

Adjust ranges to suit your data.

[excel-with-excel]

DUDE... found an easier way... 'Text to columns'

[sweep]

Glad it worked out for you, but didn't you want a formula?

Could anyone help me with a formula.......

[excel-with-excel]

Yes!!! I did ask for a formula.. Cos I thought when I had done it before at an Excel class,
I thought it was a formula! My apologies!

However, your time was not wasted, I learned new things today from your answer!
I learned that data created from formulas can be copied and pasted as values!! Very grateful !