Solved: modify formula to convert text

modify formula to convert text

4 Comments

1 Solution

110 Views

Last Modified: 2018-11-13

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

View Solution Only

Ejgil Hedegaard

CERTIFIED EXPERT

Commented: 2018-11-13

This one is on us!

(Get your first solution completely free - no credit card required)

UNLOCK SOLUTION

Ian Bellretired

Author

Commented: 2018-11-13

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

Professor JMicrosoft Excel Expert

CERTIFIED EXPERT

Top Expert 2014

Commented: 2018-11-13

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 Bellretired

Author

Commented: 2018-11-13

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

modify formula to convert text

Premium Content

Premium Content

View Solution Only

Join our community and discover your potential