Excel 2010 - Text without an end space character

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

csehzIT consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CorinTackNetwork EngineerCommented:
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.
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Santosh GuptaCommented:


CorinTackNetwork EngineerCommented:
Well, in the time I've worked with Excel, never ran into the trim function. That makes me feel rather silly.
csehzIT consultantAuthor Commented:
Thanks very much for the solutions, I also forgot somehow the Trim but so just that's all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.