modify formula to convert text

I was able to convert data from one source by using the following formula.
PROPER(SUBSTITUTE(LEFT(IG2,IFERROR(FIND("(",IG2)-1,LEN(IG2))),"'",""))
from
LAURA'S BAIRN(GB)
to
Lauras Bairn
So I tried the same formula on data from a different source and it produces something different.
from
Laura`s Bairn
to
Laura`S Bairn

Any idea how to modify it ? to return same result as in top example ( Lauras Bairn )
Many thanks
Ian

Ian,

If you have Excel 2019 or 365 version. you can use the robust formula below. it will eliminate all of the special characters and numbers and will only leave Alphabets A-Z regardless whether it lower case or upper case and a space.

=PROPER(TEXTJOIN("",TRUE,
IFS(
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="A","A",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="B","B",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="C","C",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="D","D",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="E","E",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="F","F",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="G","G",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="H","H",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="I","I",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="J","J",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="K","K",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="L","L",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="M","M",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="N","N",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="O","O",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="P","P",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="Q","Q",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="R","R",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="S","S",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="T","T",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="U","U",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="V","V",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="W","W",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="X","X",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="Y","Y",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))="Z","Z",
UPPER(MID(A1,ROW(INDIRECT(CONCATENATE("1:",LEN(A1)))),1))=" "," ",
TRUE,"")))
Remove-all-SpecChar---Numbers.xlsx

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 27

Ejgil HedegaardCommented: 2018-11-13

This will work for both
=PROPER(SUBSTITUTE(SUBSTITUTE(LEFT(IG2,IFERROR(FIND("(",IG2)-1,LEN(IG2))),"`",""),"'",""))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Ian BellretiredAuthor Commented: 2018-11-13

Hi Ejgil,
Thank you kindly, the solution is exactly what I need
Ian

Professor JMicrosoft Excel ExpertCommented: 2018-11-13

Thanks Prof.
I will look into it sometime soon.
Ian

modify formula to convert text

Who is Participating?