Excel TRIM() can't handle   in pasted HTML

  • Thread starter Thread starter dyt
  • Start date Start date
D

dyt

Has anyone seen this kind of misbehavior?

1. Create a table in HTML like:
<table><tr>
<td align="right" nowrap>214,074&nbsp;</td>
<td align="right" nowrap>55,859&nbsp;</td>
<td align="right" nowrap>26.09&nbsp;</td>
<td align="right" nowrap>267,102&nbsp;</td>
<td align="right" nowrap>71,365&nbsp;</td>
<td align="right" nowrap>26.72&nbsp;</td>
</tr></table>
(this is actually created by a very silly application
we bought)

2. Display it in your favorite browser
3. Copy it into an Excel spreadsheet
4. try to trim() or value() any of the values to get a
number. Doesn't work! What works is '=value(a1,len(a1)-
1)

!!!!
 
=substitute(a1,CHAR(160),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
dyt said:
Has anyone seen this kind of misbehavior?

1. Create a table in HTML like:
<table><tr>
<td align="right" nowrap>214,074&nbsp;</td>
<td align="right" nowrap>55,859&nbsp;</td>
<td align="right" nowrap>26.09&nbsp;</td>
<td align="right" nowrap>267,102&nbsp;</td>
<td align="right" nowrap>71,365&nbsp;</td>
<td align="right" nowrap>26.72&nbsp;</td>
</tr></table>
(this is actually created by a very silly application
we bought)

2. Display it in your favorite browser
3. Copy it into an Excel spreadsheet
4. try to trim() or value() any of the values to get a
number. Doesn't work! What works is '=value(a1,len(a1)-
1)

!!!!
Click to expand...

Why not simply open the html page in notepad and do a find and replace on
&nbsp?

/Fredrik
 
Back
Top