asked on

PROPER function amendment required please

Hi,
I would like an amendment to a PROPER function formula to remove an apostrophe
Formula is placed in A2
Please see attached.

cell A4 is correct output

Many thanks

Ian
Proper-function.xlsx

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

Try this, different quote needed.

=IF(H2="","",PROPER(SUBSTITUTE(LEFT(H2,IFERROR(FIND("(",H2)-1,LEN(H2))),"`","")))

Open in new window

»bp

Ian Bell

ASKER

Thanks guys, both do the job. Seems like the one I sent was basically right however produced a different result, not sure why.
Cheers and all the best
Ian

Roy Cox

Was the text imported or copied from a web site? This sometimes causes problems because apostrophes and speech marks don't always match what Excel requires.

If you look at the original font it doesn't match the rest of the sheet.

Ian Bell

ASKER

yes Roy it is downloaded from a database site. I still don't know why it worked for you and didn't work for me the first time as I don't see any changes made to the formula.

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ian Bell

ASKER

Thanks Bill, I will use yours, despite both working. You came up with a convincing argument :)

Bill Prew

Welcome, glad that was helpful.

»bp