0

I scraped data from a website that contains a half-million records to an Excel file. My problem is one column stores Arabic characters, and I can't apply any formatting to these cells while they contain a RIGHT-TO-LEFT MARK. I don't know it is a RIGHT-TO-LEFT MARK, but I copied the character from the cell (I can't see it) and converted it to ASCII, and got (226 128 143). I searched for this code and I found "RIGHT-TO-LEFT MARK".

I tried to find a way to search the Excel sheet by ASCII code, but I couldn't find anything.

I'm using Excel version 15.27 on Mac El Capitan.

How can I remove this character from the whole column?

2
  • What do you mean "RIGHT TO LEFT MARK"? I assume you mean this? Can you show some sample data, and a sample of the expected output? – BruceWayne Oct 21 '16 at 18:14
  • 1
    Do you just want to remove a specific character? If so, just copy it, open the "find and replace" menu. Paste it in find, and replace with nothing. – Christofer Weber Oct 21 '16 at 18:39
1

If you need to remove characters from a column you can insert a 'helper column' and use that column for the text without the offending characters, which in your case is the right-to-left mark. How you would do this depends on where the character is in the text and this is where it can also get a little different with your situation.

If the offending character is a comma for example and it is at the far right of the text you need to use =LEFT(A1,FIND(",",A1)-1) and if it is on the far left of the text you would need to use =RIGHT(A1,LEN(A1)-FIND(",",A1)).

In your case you are removing a non-text character so you need to use an ASCII code in a CHAR() reference or unicode number in a UNICHAR() reference. The right-to-left mark is under unicode 8207 so substitute the "," in the required formula above with UNICHAR(8207) making the required formula either =LEFT(A1,FIND(UNICHAR(8207),A1)-1) or =RIGHT(A1,LEN(A1)-FIND(UNICHAR(8207),A1))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.