Removal of special character from Excel sheet after data import from text file
I have a .prn file which looks like the following:
EDIVISION : PHARMA F
E ITEM : ASMAVIT PLUS TABLET 30'S F
E AREA :MUMBAI F
E HQ :MUMBAI F
It has more rows and columns but I have copied part of it for reference.
I want to remove the square box and F also the square box and E from the Excel sheet rows where I have imported this text file.
How can I do it?
Regards,
ING
First, identify the character:
A1: EDIVISION : PHARMA F
This formula returns the ASCII code of the first character:
B1: =CODE(LEFT(A1,1)
In your situation, the formula returns 27....the ASCII code for ESCAPE.
That character can't be removed using FIND/REPLACE
Next, to remove the unwanted characters, try this:
C1: =TRIM(MID(A1,3,LEN(A1)-2))
Returns: "DIVISION : PHARMA".......without the quotes.
Side Note: if you only wanted to remove the non-printable characters from the string, you could use this formula:
C1: =CLEAN(A1)
Returns: "EDIVISION : PHARMA F".......without the quotes.
Does that help?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks