Remove Characters Until a Specific Character is Reached
 

Have cells with First Last names in them - need last name only. How to delete
all characters up to and inlcuding the first blank space from left to right.
I am having difficulty because the length of the first name varies...
--
Thanks so much

Answer: Remove Characters Until a Specific Character is Reached
 

Hi there! I can definitely help you with that. You can use the LEFT and FIND functions in Excel to remove all characters up to and including the first blank space from left to right, regardless of the length of the first name. Here's how:

1. Assuming your full names are in column A, create a new column next to it (column B) where you want to display the last names only.
2. In cell B1, enter the following formula:
   Formula:

   ---

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


   ---

3. Press Enter. This formula uses the FIND function to locate the position of the first blank space in cell A1, and then subtracts that position from the total length of the cell using the LEN function. The result is the number of characters in the last name. The RIGHT function then extracts that number of characters from the right side of cell A1, giving you the last name only.
4. Copy the formula down to the rest of the cells in column B by clicking and dragging the small square in the bottom right corner of cell B1 down to the last cell in the column.

That's it! Your new column should now display the last names only. Let me know if you have any questions or if there's anything else I can help you with.

Remove Characters Until a Specific Character is Reached
 

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

--
Regards,
Dave


"Frosty 1234" wrote:

Have cells with First Last names in them - need last name only. How to delete
all characters up to and inlcuding the first blank space from left to right.
I am having difficulty because the length of the first name varies...
--
Thanks so much

Remove Characters Until a Specific Character is Reached
 

Thu, 17 May 2007 10:26:04 -0700 from Frosty 1234 <Frosty1234
@discussions.microsoft.com:
Have cells with First Last names in them - need last name only. How to delete
all characters up to and inlcuding the first blank space from left to right.
I am having difficulty because the length of the first name varies...

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

This will fail if you have any middle names, but it will work with
two-word last names like "de Souza".

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Remove Characters Until a Specific Character is Reached
 

This was exactly what I needed - worked great - Thanks,
--
Thanks so much


"David Billigmeier" wrote:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

--
Regards,
Dave


"Frosty 1234" wrote:

Have cells with First Last names in them - need last name only. How to delete
all characters up to and inlcuding the first blank space from left to right.
I am having difficulty because the length of the first name varies...
--
Thanks so much

Remove Characters Until a Specific Character is Reached
 

Tried this too and works as well - except of course on those with middle
names. Really appreciate the fast response.
--
Thanks so much


"Stan Brown" wrote:

Thu, 17 May 2007 10:26:04 -0700 from Frosty 1234 <Frosty1234
@discussions.microsoft.com:
Have cells with First Last names in them - need last name only. How to delete
all characters up to and inlcuding the first blank space from left to right.
I am having difficulty because the length of the first name varies...

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

This will fail if you have any middle names, but it will work with
two-word last names like "de Souza".

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Remove Characters Until a Specific Character is Reached
 

I am trying this formula and it tells me that I have too few arguments. what
am I doing wrong.
--
Libby


"David Billigmeier" wrote:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

--
Regards,
Dave


"Frosty 1234" wrote:

Have cells with First Last names in them - need last name only. How to delete
all characters up to and inlcuding the first blank space from left to right.
I am having difficulty because the length of the first name varies...
--
Thanks so much

Remove Characters Until a Specific Character is Reached
 

Post the *exact* formula you are trying.

Here's another way to do the same thing:

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

--
Biff
Microsoft Excel MVP


"Libby" wrote in message
...
I am trying this formula and it tells me that I have too few arguments.
what
am I doing wrong.
--
Libby


"David Billigmeier" wrote:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

--
Regards,
Dave


"Frosty 1234" wrote:

Have cells with First Last names in them - need last name only. How to
delete
all characters up to and inlcuding the first blank space from left to
right.
I am having difficulty because the length of the first name varies...
--
Thanks so much

Remove Characters Until a Specific Character is Reached
 

Works for me as written

qwerty asdfgh in A1

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) in B1 returns asdfgh

Maybe you have missed something in your tyoing?

Try copy/paste from here.

Another method you may want to try is DataText to ColumnsDelimited by space.


Gord Dibben MS Excel MVP


On Thu, 19 Jul 2007 08:08:08 -0700, Libby
wrote:

I am trying this formula and it tells me that I have too few arguments. what
am I doing wrong.