[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

Excel 2010 - Text without an end space character

Posted on 2014-08-01
Medium Priority
288 Views
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

Thanks,
TextWithoutEndSpace.xlsx
0
Question by:csehz

LVL 7

Expert Comment

ID: 40234525
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.
0

LVL 46

Accepted Solution

aikimark earned 1200 total points
ID: 40234529
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)
``````
or
``````rng.Value = RTrim(rng.value)
``````
0

LVL 13

Assisted Solution

Santosh Gupta earned 800 total points
ID: 40234533
Hi,

Try

=trim(A1)
0

LVL 7

Expert Comment

ID: 40234537
Well, in the time I've worked with Excel, never ran into the trim function. That makes me feel rather silly.
0

LVL 1

Author Closing Comment

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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month17 days, 23 hours left to enroll