Special and Hidden Characters

[razgra1z]

Hello everyone

I have a pdf file...I've converted it into excel....everything seems fine after conversion but the data has hidden spaces and quotes. For example something is written like AB1900 in original file, when I copy it and paste it in search dialog box it appears like "AB1900 "......if i write AB1900 in some other cell....excel wont compare this value to the value in converted file. I have data in single column with 1500 rows, some values are only numbers and some are combination of text and numbers. I use the function =value(trim(clean)) to get rid-off these hidden characters. But this converts the values which are only numbers, into text and I have to convert them into numbers again. I have a lot of data...is there any way to get this done in a single step rather first converting into text and then performing another step to convert text-converted numbers into numbers. Is there any way to remove hidden characters in one step and all data be converted into general format.

 

[Jon von der Heyden]

After you have done your TRIM and CLEAN, convert the results to values using Paste Special.

Then highlight each column of the trimmed and cleaned range, one at a time and:
Open Text to Columns (data tab)
Choose Delimited > Next
Uncheck All Delimiters
Click Finish

 

[razgra1z]

nice :D.....thanks alot Jon