Macro to clean cells and convert text to number

Posted on 2011-10-20
Last Modified: 2013-11-05
Hi EE,

Please refer to attached file. The content is the result of importing data from another application.
I´ve been trying to convert text to number on column E, via various methods (cell formatting, TRIM, CLEAN formulas, etc ). Nothing has worked. Then I discovered something, that may what´s causing the problem: with cursor on cell E1, if I press F2 and then click on the formula bar, I notice that the cursor blinks 2 or more positions to the right of the text. I conclude that there is garbage there, which is preventing the above mentioned method to work as expected.

So, I think a macro would help to do the job. I kindly ask for your help with the macr, or other effective way to deal with this issue. Book1.xls
Question by:capterdi
    LVL 24

    Accepted Solution

    What about a formula?

    LVL 39

    Expert Comment

    Easiest way to do without a macro, select the last "empty" character in you, copy it with Ctrl+C, do Ctrl+H to do a find replace, paste your character using ctrl+V and replace that by nothing.

    You're done.


    Author Closing Comment


    Thank you for your cooperation. I rather prefer a formula, so I can quickly copy-paste where it´s needed.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now