Easiest way to extract characters in a cell LEFT or RIGHT of a sym

[Guest]

In other programming languages, I was able to enter a function that said:

"Please extract all characters to the LEFT of this symbol (where any
character would be enclosed in quotations including a space) or the RIGHT of
this symbol."

I've been looking at some of the complex functions In this forum that are
being used to do this on a cell and they all seem overly complicated.

For example, I want to be able to point to a cell that has a First and Last
name (John Smith or Catherine Fitzgerald) and regardless of the number of
characters in either the first or last name simply produce the First OR the
Last name in the formula cell.

Sometimes the delimiter is a space, other times a comma, but I wouldn't want
to have to jump through hoops to count the number of characters left or right
of the cell contents because for each name it will be different. Similiarly,
I would want it to be flexible enough to select whatever symbol(s?) separate
the first part from the second without being overly complicated. Am I missing
something?

I can't believe what I'm seeing as solutions are all that can be done. For
now, I'm just using the text to columns to separate them out.

Any help would be much appreciated.

Cheers!
Kimberly Mills

 

[Peo Sjoblom]

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

will return what's left of the first space

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

will return what's to the right of the first space



Something generic

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

where you would put the delimiter in C1


Given how very dissimilar names can be it is basically impossible to cover
all options

 

[Niek Otten]

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

Replace the space (between the quotes) by a comma, semicolon, whatever you like.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In other programming languages, I was able to enter a function that said:
|
| "Please extract all characters to the LEFT of this symbol (where any
| character would be enclosed in quotations including a space) or the RIGHT of
| this symbol."
|
| I've been looking at some of the complex functions In this forum that are
| being used to do this on a cell and they all seem overly complicated.
|
| For example, I want to be able to point to a cell that has a First and Last
| name (John Smith or Catherine Fitzgerald) and regardless of the number of
| characters in either the first or last name simply produce the First OR the
| Last name in the formula cell.
|
| Sometimes the delimiter is a space, other times a comma, but I wouldn't want
| to have to jump through hoops to count the number of characters left or right
| of the cell contents because for each name it will be different. Similiarly,
| I would want it to be flexible enough to select whatever symbol(s?) separate
| the first part from the second without being overly complicated. Am I missing
| something?
|
| I can't believe what I'm seeing as solutions are all that can be done. For
| now, I'm just using the text to columns to separate them out.
|
| Any help would be much appreciated.
|
| Cheers!
| Kimberly Mills
|
|