Delete Spaces at the end of text in a cell

[deepakgoyal2005]

I have a series of data in a column of an excel sheet with spaces at end.
I need a formula to delete the spaces at end only.
I tried with TRIM function but it deletes spaces in beginning of text as well, which is not required.
Please Help !!!

 

[MrKowz]

Try using this custom function:

Public Function TrimRight(str As String)
    TrimRight = RTrim(str)
End Function

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">          John Jacob          </td><td style=";">          John Jacob</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=trimright(<font color="Blue">A1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[deepakgoyal2005]

Thanks MrKowz for the reply.
I had saved the VBA code in VBA Project page and the formula in adjacent cell. But it gave an error as #NAME? in the cell with the formula.

 

[MrKowz]

Did you store the code in a module in the workbook you want to run it in? (not at the worksheet or workbook level)

 

[deepakgoyal2005]

Ohh Yes !!! I pasted the code in VBA worksheet instead of a Module.
Its working now. Thanks for your help.
1 last question - Can we avoid using a macro and just use a formula to do the needful ?

 

[MrKowz]

That could be tough, depending what your data looks like. Can you please provide some examples? I'll see what I can work out.

 

[deepakgoyal2005]

The text in my columns have irregular number of spaces at end of every text entry. Need to remove the spaces at end.
I am not able to paste the part of my excel sheet as you did. I tried but was not able to know the process

 

[Scott Huish]

Perhaps:

Excel Workbook
	A	B
1	abc abc	abc abc
Sheet1

http://www.excel-jeanie-html.de/index.php?f=1

 

[majid_mx4]

Perhaps:

Sheet1

*	A	B
* *abc abc	* *abc abc

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =LEFT(A1,FIND(TRIM(A1),A1)+LEN(TRIM(A1))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

My frind
we have so many way
1=
=SUBSTITUTE(TRIM(A2);" ";" ")

2-
=RIGHT(TRIM(A2);150)
3-
for number
=SUBSTITUTE(A3;CHAR(160);"")+0

and ...

Thx Mir

 

[deepakgoyal2005]

Thank you Majid. It was helpful.