how to change text value after space into counting format

  • Thread starter Thread starter gops
  • Start date Start date
G

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
 
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
Click to expand...

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
 
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
 
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.
Click to expand...

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
 
Ron Rosenfeld said:
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.
Click to expand...

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.
 
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.
Click to expand...

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
 
Back
Top