[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
5
Medium Priority
?
288 Views
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

Printscreen
Thanks,
TextWithoutEndSpace.xlsx
0
Comment
Question by:csehz
5 Comments
 
LVL 7

Expert Comment

by:CorinTack
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

by:
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)

Open in new window

or
rng.Value = RTrim(rng.value)

Open in new window

0
 
LVL 13

Assisted Solution

by:Santosh Gupta
Santosh Gupta earned 800 total points
ID: 40234533
Hi,

Try

=trim(A1)
0
 
LVL 7

Expert Comment

by:CorinTack
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

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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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…

829 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