Fogel Posted October 17, 2012 Posted October 17, 2012 My mind is burnt so I'm thinking I just don't know how to phrase my search criteria to google, but perhaps someone here might be able to help. PRIMARY ISSUE I am trying to do a comparison of two cells in Excel. I imported the data the same way into Excel via text file but one file requires more effort in removing extraneous data. The problem is when I run a IF statement asking if the two values are equal (mix of numbers and letters) it returns false even though the two are identical. Only way I been able to fix is it to Cut one cell into Notepad and then repaste over the "original" value and then do the same for the second cell. Then magically the formula returns True. So I am guessing there is some hidden characters or it is using a different ASCII code. So the question is what is quickest way to fix this without resorting to Cutting and Pasting in Notepad for the multiple thousands of entries I have? SECONDARY ISSUES Would also be nice to know how to do the following: What is the quickest way I can remove leading spaces? (Example: " 99999999") If different than above what is the quickest way to remove spaces in general? (Example: " 99 99 99 99 99") Best way to parse/strip data from a block of varied text in excel? Share this post Link to post Share on other sites More sharing options...
BluePanda Posted October 17, 2012 Posted October 17, 2012 If I'm understanding you correctly the secondary issue can be resolved by using the following: =SUBSTITUTE(A1," ","") This essentially replaces all the spaces with nothing -- at least it's a quick way to do it too... This may help with the first issue as well. There are also some fxns to help clean up non-used ASCII characters in excel -- CLEAN() for example or TRIM()... you can also combine these with the substitute fxn to remove things as well... Share this post Link to post Share on other sites More sharing options...
Fogel Posted October 17, 2012 Posted October 17, 2012 If I'm understanding you correctly the secondary issue can be resolved by using the following: =SUBSTITUTE(A1," ","") This essentially replaces all the spaces with nothing -- at least it's a quick way to do it too... This may help with the first issue as well. There are also some fxns to help clean up non-used ASCII characters in excel -- CLEAN() for example or TRIM()... you can also combine these with the substitute fxn to remove things as well... If I could hug you right now I would hug you so hard Waco would kick my arse! Thanks so much! Share this post Link to post Share on other sites More sharing options...
BluePanda Posted October 18, 2012 Posted October 18, 2012 If I could hug you right now I would hug you so hard Waco would kick my arse! Thanks so much! lol. No worries -- just glad I could be useful Share this post Link to post Share on other sites More sharing options...
ccokeman Posted October 18, 2012 Posted October 18, 2012 lol. No worries -- just glad I could be useful Excellent, now I know who to go to for Excel questions! Share this post Link to post Share on other sites More sharing options...
Fogel Posted October 19, 2012 Posted October 19, 2012 I still have a need to parse through blocks of text to extract what I want, but I am gonna try and figure that out next week. If I fry my brain next week, expect a question, or two, or three next week. Share this post Link to post Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now