# convert special charatters

#### macro001

##### New Member
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

Last edited:

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Tim_Excel_

##### Well-known Member
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 '.

Last edited:

#### macro001

##### New Member
i not know how many special charrater i have

but i'm sure i have also some other

#### deciog

##### Board Regular
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

##### MrExcel MVP
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

##### New Member
but im use excel in italian and i not find translation of LIN(\$1:\$1000) what is this?

#### mikerickson

##### MrExcel MVP
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

##### New Member
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

##### Board Regular
Replaces LIN with ROW

Decio

Replies
0
Views
199
Replies
2
Views
279
Replies
10
Views
293
Replies
3
Views
112
Replies
1
Views
253

1,190,796
Messages
5,982,971
Members
439,810
Latest member
phobo3s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back