format into numbers from webpage

[GabbyU]

I'm trying to copy/paste my credit card transactions from a webpage into a
worksheet, because the website doesnot provide a facility for downloading
the details. I'm using Office 2003. Using Opera, my Excel Paste Special...
options are Unicode text and text, both of which produces undesirable
effects in that each field is shown on separate lines, for example:

date
name
description
amount

Using IE, the Paste Special... options has an additional HTML option. Using
this option, the transactions appear correctly line by line, and each field
had their own columns; for example:

date(colA) name(colB) description(colC) amount(colD)

My problem is that the amount entry (eg $25.45) is not treated as a number;
it is left-justified and does not sum up, using functions or any of Excel's
way of showing sums. Using the Format cells... options does not help, nor
does the Data>Text to Columns... facility. Deleting the $ sign from the cell
does not help either. Because I can still edit the cell, I'm guessing that
the cell does not contain graphics, although the background color from the
webpage did transfer over to the cells.

How can I convert the cell entries to numbers without re-keying the amount?

TIA

 

[Ron Rosenfeld]

How can I convert the cell entries to numbers without re-keying the amount?

Try this formula:

=--SUBSTITUTE(A1,CHAR(160),"")

(Change A1 to your cell).

HTML frequently adds a non-breaking space to the end of a number.


--ron

 

[GabbyU]

Ron,

I tried your formula but it didn't work, but with your clue I now noticed a
space before the $ sign, and by removing that space, excel now recognises
the cell as a number. Using the Character map, and the Wingdings font, the
Char Code is most likely "0xA0", a tiny square (about the 8th col, 7th row)
in the Character Map for Wingdings.

How do I find out what is the char(nnn) I would use in your SUBSTITUTE
formula ?

Thanks...Gabby

 

[Dave Peterson]

You could use a formula like:

=CODE(MID(A1,x,1))

where x represents the character in the string (1st, 2nd, ...)

If you have to it lots, you may want to get an addin that Chip Pearson wrote:
http://www.cpearson.com/excel/CellView.htm

 

[Ron Rosenfeld]

Ron,

I tried your formula but it didn't work, but with your clue I now noticed a
space before the $ sign, and by removing that space, excel now recognises
the cell as a number. Using the Character map, and the Wingdings font, the
Char Code is most likely "0xA0", a tiny square (about the 8th col, 7th row)
in the Character Map for Wingdings.

How do I find out what is the char(nnn) I would use in your SUBSTITUTE
formula ?

Thanks...Gabby

Interesting.

0xA0 *IS* 160 !!!

If there were more than one, the SUBSTITUTE should have removed them all and
the formula should have worked as written.

Maybe there are some other funny characters in there.

We could write a VBA routine but I'd like to see what's in there.

Try this.

With your data in A1 (or some cell), enter the following formula in Row 1 (any
column, but must be the first row):

=CODE(MID($A$1,ROW(),1))

Note that the cell reference has the $'s indicating an absolute reference.

Then copy/drag that formula down until you start to get #VALUE! errors.

Copy/paste the results and post it back here.



--ron

 

[GabbyU]

Dave,

Using your CODE formula and the CellView addin, the 1st position is indeed
CHAR(160). On closer examination, using Ron's SUBSTiTUTE formula below did
get rid of the 1st position, however excel continues to treat it as a
non-number.
Is this because SUBSTITUTE creates a text result, and I would have to mould
that result into a number before I can add it up? If so, kindly how me how.

TIA...Gabby

You could use a formula like:

=CODE(MID(A1,x,1))

where x represents the character in the string (1st, 2nd, ...)

If you have to it lots, you may want to get an addin that Chip Pearson wrote:
http://www.cpearson.com/excel/CellView.htm

Ron,

I tried your formula but it didn't work, but with your clue I now noticed a
space before the $ sign, and by removing that space, excel now recognises
the cell as a number. Using the Character map, and the Wingdings font, the
Char Code is most likely "0xA0", a tiny square (about the 8th col, 7th row)
in the Character Map for Wingdings.

How do I find out what is the char(nnn) I would use in your SUBSTITUTE
formula ?

Thanks...Gabby

 

[GabbyU]

Ron,

Col A is amount, and col B is your formula pasted all the way down.

$63.04 160
$141.58 36
$26.20 54
$86.30 51
$50.73 46
$61.04 48
$177.56 52
$341.30 #VALUE!
$76.44 #VALUE!
$119.92 #VALUE!
$23.70 #VALUE!
$35.94 #VALUE!
$136.36 #VALUE!
$218.46 #VALUE!
$2,002.03 #VALUE!
$120.78 #VALUE!
#VALUE!
#VALUE!


Also note my reply to Dave on prev thread:

"Using your CODE formula and the CellView addin, the 1st position is indeed
CHAR(160). On closer examination, using Ron's SUBSTiTUTE formula below did
get rid of the 1st position, however excel continues to treat it as a
non-number.
"Is this because SUBSTITUTE creates a text result, and I would have to mould
that result into a number before I can add it up? If so, kindly how me how."

 

[Ron Rosenfeld]

Ron,

Col A is amount, and col B is your formula pasted all the way down.

$63.04 160
$141.58 36
$26.20 54
$86.30 51
$50.73 46
$61.04 48
$177.56 52
$341.30 #VALUE!
$76.44 #VALUE!
$119.92 #VALUE!
$23.70 #VALUE!
$35.94 #VALUE!
$136.36 #VALUE!
$218.46 #VALUE!
$2,002.03 #VALUE!
$120.78 #VALUE!
#VALUE!
#VALUE!


Also note my reply to Dave on prev thread:

"Using your CODE formula and the CellView addin, the 1st position is indeed
CHAR(160). On closer examination, using Ron's SUBSTiTUTE formula below did
get rid of the 1st position, however excel continues to treat it as a
non-number.
"Is this because SUBSTITUTE creates a text result, and I would have to mould
that result into a number before I can add it up? If so, kindly how me how."

When you copied my formula, did you include the double unary?

I posted the formula as

=--SUBSTITUTE(A1,CHAR(160),"")

If you did not copy it exactly, then it will not convert the result to text.


--ron

 

[GabbyU]

Ron,

My apologies...before I must have omitted the two negative signs before the
SUBSTITUTE function (I presume this is what you mean by "double unary"). I
have deleted the old sheets and started with a clean slate when I sent you
the last reply.

Yes it now works !!! with your formula pasted as is. I guess this is the
mathematical shortcut of converting a text number to a numeric number,
right?

I guess the fact that your signature also has a "double unary" confused me
;-). Many thanks to you and Dave. I have certainly learnt a lot tonight;
worth working till 5am.

Gabby

 

[Dave Peterson]

David McRitchie has a macro that cleans up this kind of thing, too.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Ron Rosenfeld]

Yes it now works !!! with your formula pasted as is. I guess this is the
mathematical shortcut of converting a text number to a numeric number,
right?
Correct!


I guess the fact that your signature also has a "double unary" confused me
;-). Many thanks to you and Dave. I have certainly learnt a lot tonight;
worth working till 5am.

Fortunately (for me), I was not working until 5AM here ).

Thanks for the followup.


--ron

 

[GabbyU]

Dave, thanks for the webpages...I have bookmarked them.

David McRitchie has a macro that cleans up this kind of thing, too.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron,

My apologies...before I must have omitted the two negative signs before the
SUBSTITUTE function (I presume this is what you mean by "double unary"). I
have deleted the old sheets and started with a clean slate when I sent you
the last reply.

Yes it now works !!! with your formula pasted as is. I guess this is the
mathematical shortcut of converting a text number to a numeric number,
right?

I guess the fact that your signature also has a "double unary" confused me
;-). Many thanks to you and Dave. I have certainly learnt a lot tonight;
worth working till 5am.

Gabby