rEMOVE SPACES

[vikas0577]

hI,
Is there a formula to remove spaces at the end of Text or numbers in the cells. for eg. I have got data as under

<TABLE style="WIDTH: 73pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=97 border=0 x:str><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl22 id=td_post_256286 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=97 height=21>72200000.00 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>1000000.00 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>132283.00 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>59549.00 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>121534.00 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>16545.00 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>500.00 </TD></TR></TBODY></TABLE>

There are 2 spaces at the end of each cell value.
How to remove them.
Pl. help.
Thanks
Vikas

 

[vikas0577]

Dear Sektor,
I have pasted as per your steps, but does not work,
Can U explain step 5, I may be wrong on step 5

I am getting the RemSp in the run Macro, but nothing works on the cells.

 

[Sektor]

Did you activate sheet with those numbers? Activate this sheet, press Alt+F8, select "RemSp" and press Run button.
BTW, what did you expect to see? Spaces are removed but visually you won't see this. To make sure they are removed, activate any cell with number and press F2. You should see that the last character is not a space.

 

[pgc01]

Hi

If the cells have only the numbers use Find/Replace and replace space with nothing.

 

[vikas0577]

Hi,
Thanks for reply , Pl. try it your self , I have tried it , dosent work.

If U have anyother solution pl. let me know.
Thanks
Vikas

Hi

If the cells have only the numbers use Find/Replace and replace space with nothing.

 

[vikas0577]

Dear Sektor,

It dose not work, Pl. provide me your email id so that I can mail you the xl file , then may be U can solve the issue.
Thanks
Vikas

 

[Sektor]

See private message.

 

[SuperFerret]

Have you tried copying one of the 'spaces' and using this in Find/Replace instead?

I had some issues yesterday with some spaces that weren't spaces and that seemed to solve it.

 

[ganeshpoojary05]

assuming that your data is in A1. Try the below formula:

=LEFT(A1,LEN(A1)-2)

or if you want to remove any spaces (can be one or more spaces) from the end. You can try the below formula:

=SUBSTITUTE(A1," ","")

 

[vikas0577]

Thanks SuperFerret,

this solve my issue.
Thanks A Lot.

 

[vikas0577]

Thanks Sektor,

I had emailed the file to U but the problem is solved.

Thanks
Vikas