Formula for deleting text in cell

[PuzzledK]

Hello. There must be a way to do this, but I'm not experienced in Excel, so would really appreciate any help.

I have to type up a list at my work, but the report prints it to the cell with last name and first name. How can I enter a formula that will delete the first name? I only want doe in that cell and delete the /john

 

[shift-del]

Hi

=SUBSTITUTE(D1,"/john","")

 

[PuzzledK]

Hi

=SUBSTITUTE(D1,"/john","")

Thank you for your answer, but I don't think that will work. Not all the names are john, just using that as an example. I think I have to do something with a great or less than 0 after the /

 

[shift-del]

It is always good to give proper examples.

 

[shift-del]

=LEFT(D1,SEARCH("/",D1)-1)

 

[Peter_SSs]

Welcome to the MrExcel board!

If you actually want to delete the first names from that column then you can do them all at once

1. Select the column by clicking its letter label
2. Find/Replace (Ctrl+H or via the ribbon) with
   Find what: /*
   Replace with: leave blank
   Replace All

 

[Peter_SSs]

If you didn't want to change the original data like that you could still do them all at once

1. Select column D
2. Data ribbon tab -> Text to Columns -> Delimited -> Next -> Clear all the delimiter boxes except 'Other' and put a forward slash in the box to the right of 'Other' -> Next -> Destination: E1 -> Click in the column at the bottom with the first names (it will go black) then select 'Do not import' above -> Finish

 

[PuzzledK]

Welcome to the MrExcel board!

If you actually want to delete the first names from that column then you can do them all at once

1. Select the column by clicking its letter label
2. Find/Replace (Ctrl+H or via the ribbon) with
   Find what: /*
   Replace with: leave blank
   Replace All

Worked like MAGIC! Thank you so much.

 

[PuzzledK]

If you didn't want to change the original data like that you could still do them all at once

1. Select column D
2. Data ribbon tab -> Text to Columns -> Delimited -> Next -> Clear all the delimiter boxes except 'Other' and put a forward slash in the box to the right of 'Other' -> Next -> Destination: E1 -> Click in the column at the bottom with the first names (it will go black) then select 'Do not import' above -> Finish

Thank you for your time Peter. I really appreciate it.

 

[Peter_SSs]

Worked like MAGIC! Thank you so much.

You're welcome. Thanks for the confirmation.