how to change text value after space into counting format

[gops]

I m working in a Bank document, which i have downloaded
from internet. It is in a mix format of excel and html.
There is a space after Values in it, so when I totalling
its amout field into an excel file it's not supporting
the formula, if i remove the space manually then it
calculate the values.

please solve my problem.

gops

 

[Ron Rosenfeld]

I m working in a Bank document, which i have downloaded
from internet. It is in a mix format of excel and html.
There is a space after Values in it, so when I totalling
its amout field into an excel file it's not supporting
the formula, if i remove the space manually then it
calculate the values.

please solve my problem.

gops

In an HTML file, it is common to have an extra character at the end. I'm not
sure why. However, assuming =TRIM(value) doesn't work, try:

=SUBSTITUTE(value,CHAR(160),"")


--ron

 

[David McRitchie]

The why:
In HTML there is frequently a space or (non-breaking) space
to the right of numbers or to the left of text so that it won't tounch
the border. There is cell padding available but that affects both
verical and horizontal so cell padding doesn't work well for this.

Macro solutions:
You would do much better with a macro see TRIMALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
If you are working from a bank statement you may also be pulling
shapes (pictures, icons, jpg files) that you don't want, see
delShapesSel (based on upper left corner), or delShapesOnSht in
http://www.mvps.org/dmcritchie/excel/shapes.htm

 

[Ron Rosenfeld]

The why:
In HTML there is frequently a space or (non-breaking) space
to the right of numbers or to the left of text so that it won't tounch
the border. There is cell padding available but that affects both
verical and horizontal so cell padding doesn't work well for this.

That is an interesting design decision. I suppose the alternative would be to
have a fixed distance from the border, sort of like a defined gutter, but I
guess that might be less flexible.

Thanks for that info.


--ron

 

[Harlan Grove]

That is an interesting design decision. I suppose the alternative would
be to have a fixed distance from the border, sort of like a defined
gutter, but I guess that might be less flexible.

AFAIK, there's no gutter width capability in HTML tables, so the only
alternative would be adding narrow columns with left or right but not both
borders missing. That could make for significantly larger HTML files, so a
good idea they're not used.

If Excel faced any real competition, Microsoft might actually provide a
couple of new worksheet functions in the next version to deal with this,
perhaps a paramerized extended TRIM with a second parameter to specify what
and how to trim.

 

[Ron Rosenfeld]

If Excel faced any real competition, Microsoft might actually provide a
couple of new worksheet functions in the next version to deal with this,
perhaps a paramerized extended TRIM with a second parameter to specify what
and how to trim.

A good idea. But easy enough to implement in VBA. However, it seems there are
so many questions about this issue in these NG's that it would be better if in
were the native Excel.


--ron