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

PROPER(SUBSTITUTE(LEFT(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

SpreadsheetsMicrosoft ExcelMicrosoft Office

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,

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

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(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

UPPER(MID(A1,ROW(INDIRECT(

TRUE,"")))

Thanks Prof.

I will look into it sometime soon.

Ian

I will look into it sometime soon.

Ian

Thank you kindly, the solution is exactly what I need

Ian