• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Space and Text format issue into file downloaded from software

N

neeraj2050

Member
Greetings!!
I download data from software. It has two issues :
1)When I open that file(excel) and click in a cell, it consists of n number of spaces, i.e “Albert ”. It affects Vlookup as data into other sheet do not have those extra spaces. Every time I have to apply Trim and then only I get the formula worked
2) Every cell is formatted into text. If I enter any formula into blank cell then it shows formula only into that cell rather than the result of that formula. Every time I have to press Ctrl+Shift+1
Is there any shortest way out
Thanks in advance!!
 
1) Using Trim/ Substitute/ Replace is the only way to remove the extra spaces
2) Format the column to General then write your formula
 
Why not check with the people who are exporting if there are some settings to handle this in the source software?

If there's no option in the source software then you can think of writing a macro to process these things (if you have to do this periodically).

For VLOOKUP failure, a formula can be written which can ignore spaces and return match (search for LOOKUP+SEARCH combination formula if you want this).
 
You must log in or register to reply here.
Back
Top