Excel Programing Problem with names

[BIG49010]

I have a problem with horses with apostrophes ' in the name can anybody tell me an easyway to remove them?

Thanks

Big

[Grifter]

BIG -- Don't know why you'd want to remvove the apostrophes, but..... the following ought to work (assume cell A1 has Horse's Ass in it):


=CONCATENATE(LEFT(A1,FIND("'",A1)-1),MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)+1))
-- Grifter

[BIG49010]

I'll give it a try thanks.

[cj]

Highlight the entire column that contains horses names by clicking on the letter at the top of the column. Then press Control H to bring up the Replace window.

In the Find What box, enter '

In the Replace With, either leave it blank or enter a space if you want the space to be there, then press replace all.

[BIG49010]

It works fine, it gives me what I need, but if there is no horse with apostrophes, I get error message.

How can I handle this, and also can I put Proper command in with this also?

[jfdinneen]

Big,

Adapting Grifter's solution, try:
=IF(NOT(ISERROR(FIND("'",A1,1))),CONCATENATE(LEFT(A1,FIND("'",A1)-1),MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)+1)),A1)

Best wishes,

John

[hcap]

A slight variation


Put in A1
Horse's Ass

Put in B1
=SEARCH("'",A1)

Put in C1

=IF(ISERROR(B1),PROPER(A1),PROPER(CONCATENATE(LEFT(A1,FIND("'",A1)-1),MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)+1))))

[hcap]

Sorry, to copy the formula into excel remove the spaces after
(CONCATENATE(LEFT..

[Tom]

Why remove the ' ?

[Red Knave]

Why remove the ' ?If he's saving anything to a SQL database, or using the name in a query it will crap out. The ' is like an end of field mark or something.

[BIG49010]

For some reason in Excel I can't get the program I put together to work on horses if they have apostrophes, so I was looking through the files for horses that had them. I did this by hand and it took a long time to process files, now thanks to everybody, I did 30 days in about an hour, that would have taken me two days.

Thanks again everyone for your help, the power of the internet!:cool:

[edek]

=SUBSTITUTE(A1,"'","") is cleaner.

or

=PROPER(SUBSTITUTE(A1,"'",""))