Find & and delete everything following
 

Hi There,
I have a spreadsheet with names in one column, however to load this file I
need to format correctly.
EG column contains:
James Smith & Sarah Brown
I need to delete everything from '&' onwards.
(I have used text to columns using & as the delimiter, however there are
instances of three or four names!)
Is there are delete function I can use? Could you please help?
Thanks in advance!

Find & and delete everything following
 

Hi,

Try this

=LEFT(EG3,SEARCH("&",EG3,1)-2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"fleura26" wrote in message
...
Hi There,
I have a spreadsheet with names in one column, however to load this file I
need to format correctly.
EG column contains:
James Smith & Sarah Brown
I need to delete everything from '&' onwards.
(I have used text to columns using & as the delimiter, however there are
instances of three or four names!)
Is there are delete function I can use? Could you please help?
Thanks in advance!

Find & and delete everything following
 

YAYYYY, you just saved me hours of manual work. Thank you thank you thank
you!!!!!!!

"Ashish Mathur" wrote:

Hi,

Try this

=LEFT(EG3,SEARCH("&",EG3,1)-2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"fleura26" wrote in message
...
Hi There,
I have a spreadsheet with names in one column, however to load this file I
need to format correctly.
EG column contains:
James Smith & Sarah Brown
I need to delete everything from '&' onwards.
(I have used text to columns using & as the delimiter, however there are
instances of three or four names!)
Is there are delete function I can use? Could you please help?
Thanks in advance!

Find & and delete everything following
 

You may want to search for " &" or add =trim() around that function.

Another possible way is to
Select the column
Edit|replace
what: & (or spacebar ampersand)
with: (leave blank)
replace all

This suffers the same problem if there are varying number of spaces in front of
that ampersand.

fleura26 wrote:

YAYYYY, you just saved me hours of manual work. Thank you thank you thank
you!!!!!!!

"Ashish Mathur" wrote:

Hi,

Try this

=LEFT(EG3,SEARCH("&",EG3,1)-2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"fleura26" wrote in message
...
Hi There,
I have a spreadsheet with names in one column, however to load this file I
need to format correctly.
EG column contains:
James Smith & Sarah Brown
I need to delete everything from '&' onwards.
(I have used text to columns using & as the delimiter, however there are
instances of three or four names!)
Is there are delete function I can use? Could you please help?
Thanks in advance!



--

Dave Peterson

Find & and delete everything following
 

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"fleura26" wrote in message
...
YAYYYY, you just saved me hours of manual work. Thank you thank you thank
you!!!!!!!

"Ashish Mathur" wrote:

Hi,

Try this

=LEFT(EG3,SEARCH("&",EG3,1)-2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"fleura26" wrote in message
...
Hi There,
I have a spreadsheet with names in one column, however to load this
file I
need to format correctly.
EG column contains:
James Smith & Sarah Brown
I need to delete everything from '&' onwards.
(I have used text to columns using & as the delimiter, however there
are
instances of three or four names!)
Is there are delete function I can use? Could you please help?
Thanks in advance!

Find & and delete everything following
 

Without using a formula, you can use find & replace, replacing &* with
nothing (that's ampersand & asterisk)
Bob Umlas
Excel MVP

"Ashish Mathur" wrote in message
...
You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"fleura26" wrote in message
...
YAYYYY, you just saved me hours of manual work. Thank you thank you thank
you!!!!!!!

"Ashish Mathur" wrote:

Hi,

Try this

=LEFT(EG3,SEARCH("&",EG3,1)-2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"fleura26" wrote in message
...
Hi There,
I have a spreadsheet with names in one column, however to load this
file I
need to format correctly.
EG column contains:
James Smith & Sarah Brown
I need to delete everything from '&' onwards.
(I have used text to columns using & as the delimiter, however there
are
instances of three or four names!)
Is there are delete function I can use? Could you please help?
Thanks in advance!