convert special charatters

[macro001]

Hi
I have some data with special charatters like this:
all& # 039;
(without spaces)

I like to convert this in
all'

how do this?
thx

 

[Tim_Excel_]

Do all cells contain exactly all& # 039; ? Or is there some other form of consistency in the cells values?

If I understand correctly, you want to remove the special characters and replace them for a single '.

 

[macro001]

i not know how many special charrater i have

but i'm sure i have also some other

 

[deciog]

Macro001


Check if this formula is what you want


=MID(REPLACE(A1,LARGE(IF(CODE(MID(UPPER(A1)&REPT(" ",1000),LIN($1:$1000),1))>64,IF(CODE(MID(UPPER(A1)&REPT(" ",1000),LIN($1:$1000),1))<91,LIN($1:$1000))),1)+1,1000,""),SMALL(IF(CODE(MID(UPPER(A1)&REPT(" ",1000),LIN($1:$1000),1))>64,IF(CODE(MID(UPPER(A1)&REPT(" ",1000),LIN($1:$1000),1))<91,LIN($1:$1000))),1),1000)




Decio

 

[mikerickson]

I'm confused about exactly what the data is.

Do you have a text string that includes the & # and ; characters.

If your data is in A1 does =CODE(A1,3,1) return 38 ?

 

[macro001]

thx for answer
but im use excel in italian and i not find translation of LIN($1:$1000) what is this?

 

[mikerickson]

Try using this formula, if you have multiple special characters, it may need to be dragged right

=IFERROR(REPLACE(A1,FIND("&#",A1),6,CHAR(MID(A1,FIND("&#",A1)+2,3))), A1&"")

 

[macro001]

I'm confused about exactly what the data is.

Do you have a text string that includes the & # and ; characters.

If your data is in A1 does =CODE(A1,3,1) return 38 ?

HI
you know this charatters like this & ' and other , but in my csv i have some like this
& # 039 (without spacing) and other

 

[deciog]

macro001

https://www.sendspace.com/file/x43ro2

Modelo

Decio

 

[deciog]

Replaces LIN with ROW

Decio