{"id":1035,"date":"2017-11-30T11:10:31","date_gmt":"2017-11-30T03:10:31","guid":{"rendered":"http:\/\/owa.sodexo-cn.com\/blog\/?p=1035"},"modified":"2017-11-30T11:15:01","modified_gmt":"2017-11-30T03:15:01","slug":"excelto-get-rid-of-the-unnecessary-whitespace-in-the-cell-you-might-to-try-this-method","status":"publish","type":"post","link":"https:\/\/owa.sodexo-cn.com\/blog\/?p=1035","title":{"rendered":"[Excel]To get rid of the unnecessary whitespace in the cell, you might to try this method"},"content":{"rendered":"

What do you usually do when the VLOOKUP function is always fed back to #N\/A?
\n<\/span><\/p>\n

We usually check if there are any unnecessary special symbols in the cells that need to be matched, and most often there is no necessary space symbol.
\n<\/span><\/p>\n

Space issues
\n<\/span><\/p>\n

Yes, it’s a space.
\n<\/span><\/p>\n

In general, these three kinds of unnecessary spaces appear:
\n<\/span><\/p>\n

1. Starting bit space
\n<\/span><\/p>\n

2. Too many spaces between words
\n<\/span><\/p>\n

3. Final space<\/span>
\n\"\"
\n<\/span><\/p>\n

Perhaps, you will immediately a<\/span>nswer: “It’s easy, using the <\/span>find&replacement or substitute function.” <\/span>
\n<\/span><\/p>\n

Is it true? Let’s take a look at the results of using the <\/span>find&replacement or the substitute function:
\n<\/span><\/span><\/p>\n

\"\"
\n<\/span><\/p>\n

Well, it looks like the result is not ideal. When you use a space-deletion method to handle such information, the normal spaces between the words are also deleted.
\n<\/span><\/p>\n

What to do?
\n<\/span><\/p>\n

Trim function
\n<\/span><\/p>\n

The trim function<\/span>, it can remove all the spaces in the text except for a single space between words.
\n<\/span><\/span><\/p>\n

It is especially suitable for processing some irregular spacing text obtained from other data sources.
\n<\/span><\/p>\n

Expression: TRIM (text)
\n<\/span><\/p>\n

\"\"
\n<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

What do you usually do when the VLOOKUP function is alw … \u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-1035","post","type-post","status-publish","format-standard","hentry","category-tips"],"views":2230,"_links":{"self":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1035","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1035"}],"version-history":[{"count":2,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1035\/revisions"}],"predecessor-version":[{"id":1037,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1035\/revisions\/1037"}],"wp:attachment":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1035"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1035"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1035"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}