I have a sheet with names and email addresses in column a and b.

The names are in the format John Smith, and the email addresses are email addresses.

I need a formula that will separate forename from surname, and deposit them elsewhere, but still corresponding visually with the email address.

Basically, something that converts this

John Smith | john@smith.com

to this

John | Smith | john@smith.com

(where | is a separate column)

Help! and thanks in advance!!!

you can use the FIND, LEFT, RIGHT and LEN functions to control text separation (find the space and cut the left and right portions from the string around the space) or for a one off separation use “Text to Columns” from the menu bar (DATA menu item)

to split a string with a single space:

=LEFT(A1, FIND(” “, A1, 1))

and

=RIGHT(A1, LEN(A1)-FIND(” “, A1, 1))

You will need to manually check for errors such as when you have 2x spaces or two first names etc. under either technique

Try this in cell A3 =left(A1,(find(” “,A1)-1))
And this in A4 = right(A1,(len(A1)-find(” “,A1)))

Sorry from memory and don’t have excel to hand but have had to do stuff like that in the past

Stoner, you’re a star. Cheers!

Stoner wins the excel geek prize

or you could use SUBSTITUTE to sub . @ etc for a * then use text to columns on the *

sorry i misread question!

would [DATA] [text to columns] not have worked?

Charlie, yeah it did. Damo, I’ve no idea what you’re on about!

move over you lot, I’ll handle this

so, Pook, it’s error bars you’re after, yeah ?

😉

I’ve made enough errors at bars not to get involved in such tomfoolery and shenanigans thankyou very much Mr Pants.
😕

or did you want boxplots?

Are all the names just the two words? If you have middle names or middle initials etc finding the last word will be a bit harder.

Use something like this:
=if(iserr(find(” “,a1)),a1,right(a1,len(a1)-find(“*”,substitute(a1,” “,”*”,len(a1)-len(substitute(a1,” “,””))))))

all good, i would have had to export to txt file and reimport with spaces as separators…

Any names with more than one space can be sorted by using the TRIM function once you’ve chopped them up.

+1 For Data | Text to Columns | Fixed Width | Insert Break Line

Surely Data | Text to Columns |Delimited – Space as separator

While we are at it, who knows how to represent the percentage of individual items for the range? Just compiling an accident/incident record and have about 65 rows with the details of each accident/incident. I need to show the numbers for each group and the percentage but can’t figure out the statistic formulas. Help much appreciated 🙂

If there are groups within the range, it would be easiest to use a pivot table.
If you just wang percentages for the whole range, sum the total into a cell and divide by the individual entry.

While we are at it, who knows how to represent the percentage of individual items for the range? Just compiling an accident/incident record and have about 65 rows with the details of each accident/incident. I need to show the numbers for each group and the percentage but can’t figure out the statistic formulas. Help much appreciated

What you need if i understand your question is the frequency function. Allows you to create histograms and the like by grouping the data in to bands.