remove partial text from cell

**D Casten** · 04-25-2012, 11:52 AM

I may be barking up the wrong tree but ...

I am copying text (contest results from another website) and posting into excel. As it is formatted on the original site, it posts the place, followed by a period, then the team name, then a dash, then the score.
examp ... 4. Smokers Purgatory - 662.2858

To place it in our database we need the period removed from the place, and the dash and score removed.
axamp ... 4 Smokers Purgatory

Is this possible to automate in excel or is there a way to format so that excel seperates the place, team name and score?
Or should I be looking for a different method?

(It is not an option to have the original web site change their posted format)

**DGagnon** · 04-25-2012, 12:05 PM

you can do something like this:

=LEFT(SUBSTITUTE(A1,".",""),FIND(" - ",SUBSTITUTE(A1,".",""))-1)
where A1 is the text value.

**Pete_UK** · 04-25-2012, 12:05 PM

You could put this formula in the next column:

=SUBSTITUTE(LEFT(A1,SEARCH("-",A1)-1),".","")

and then copy it down. You could also fix the values and then delete the original column.

Hope this helps.

Pete

**D Casten** · 04-25-2012, 12:21 PM

Brilliant!
Thank you very much!

**DGagnon** · 04-25-2012, 12:34 PM

Could you please mark this issue as resolved?

**shawnoot** · 07-13-2014, 10:47 PM

Hi guys, I am having the exact same situation except the the value is "11/5/2014 12:46:44 pm".
I want to remove the date and retain the time.

I've tried the formula provided by AMDGagnon & AMPete_UK but it's not working. I am not sure where did I go wrong.