  asked on

# 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 Last Comment
Ian Bell

8/22/2022 - Mon
Ejgil Hedegaard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Ian Bell

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

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
Ian Bell