You can distinguish the cases using the tool > ‘View’ > ‘ValueHighlighting’ (Ctrl+F8) or by asking =TEXT(A1) in any blank cell. After clicking Yes, you can see all the empty lines are removed form the document. Then a prompt box will pop out to remind you removing all empty lines, please click Yes button, see screenshot: 3. If the “space” actually was inserted as ‘Thousands Separator’ by number formatting, refer to the answer by Samyn. Please apply this utility by clicking Kutools > Empty Paras > Remove Empty Manual Line Breaks. My answer is based on the assumption that the original content was text (imported from a csv e.g.). The results of SUBSTITUTE are texts anyway, and a formula would not work in a ‘Text’ formatted cell. Put into B1 (e.g.) the formulaĭone! In this case the ‘Numbers’ format of the target cell must not be ‘Text’. Suppose your text containing the unwanted spaces is in A1. Shall it be done by a formula in another (adjacent?) cell, and be active there even if the original content is changed? If the result shall not be recognized as a number (but remain text), you may have to select the option ‘Text’ (code under ‘Format Cells…’ > ‘Numbers’ > ‘Category’ in advance. Tick ‘Current selection only’ if needed and order ‘Replace All’. Put * (a space followed by an asterisk) into ‘Search For’ and leave ‘Replace With’ empty. Use the tool ‘Find & Replace’, enable the option ‘Regular expressions’, Shall it be done in situ, once and for all?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |