Removing the second part of data in a cell

[tonywatsonhelp]

Hi Everyone

Ok so I need to remove the name from a cell with other data in it.

so lets say data is in A2

here are some examples of what it can look like

what I have: The result I want
WEb Site - Sales - Tony Tony
Web - enquiry - Tony-Snith Tony-Snith
Web equiry - Tony Tony
web Enquiry - Tony-smith Tony-smith

So basicly there is a problem

I need to seperate the data by finding the last " - " but some names have just "-" which is fine and part of what i need so how can I text the formula to
"Split data where the the last occurance of " - " (so thats "Space-Space") and give me everything to the right?

thanks

Tony

 

[ranman256]

put the cursor in the top row of the data column, then run:

sub ExtractNames()
dim vWord

While ActiveCell.Value <> ""
    vWord=  ActiveCell.Value 
    ActiveCell.Offset(0, 1).value = mid(vWord,instrRev(vWord,"-")+1)
     
     ActiveCell.Offset(1, 0).Select  'next row
Wend
end sub

 

[tonywatsonhelp]

Hi ranman,

But for reason I wont go into I need this as a formula not vba just relised I did say that sorry.

Thanks

Tony

 

[Joe4]

Try this (for an entry in cell A1):

=TRIM(RIGHT(SUBSTITUTE(A1," - ",REPT(" ",100)),100))

 

[tonywatsonhelp]

Thank you Joe,
this is perfect

Thanks

Tony

 

[Joe4]

You are welcome!