Excel 2010 - Text without an end space character

Posted on 2014-08-01
Last Modified: 2014-08-01
Dear Experts,

Can you please advise basically I have a file where in a column there are unwaited space characters at some texts.

Could you advise based on the example which formula could cut that space from the end and leave the text (there the Orange has it), if the space is in the middle somewhere that should be untouched

Question by:csehz
    LVL 7

    Expert Comment

    Presuming you'll only ever have the 1 extra space at the end, the following formula would work (the example here is looking specifically at the cell with the word Orange in it, so it's A3):

    =MID(A3,1,(FIND(" ",A3,(LEN(A3)-1))))

    What this does is uses the 'Mid' function to get the string from that cell, starting at character 1, and ending at the last character before the extraneous space. So if you put this formula in, it would grab the first 6 characters from the word 'Orange ' making it just 'Orange' without the space.
    LVL 44

    Accepted Solution

    Have you tried the Trim() function in a formula?

    In VBA, you have Trim(), RTrim(), and LTrim() functions.  You could define a range and do this:
    rng.Value = Trim(rng.value)

    Open in new window

    rng.Value = RTrim(rng.value)

    Open in new window

    LVL 13

    Assisted Solution

    by:Santosh Gupta


    LVL 7

    Expert Comment

    Well, in the time I've worked with Excel, never ran into the trim function. That makes me feel rather silly.
    LVL 1

    Author Closing Comment

    Thanks very much for the solutions, I also forgot somehow the Trim but so just that's all

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    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,…
    This article will show you how to use shortcut menus in the Access run-time environment.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now