Trying to find a way to add up Hyperlinks or remove and then let me add

There is a file we pull from a system and it gives back the amounts in hyperlink form, however, I am trying to add those all up without having to remove or retype the hyperlinks amounts. I was wondering if anyone could help me out. Ive tried the "copy 1 paste special trick", it worked but would not let add them up and only counted. Have to do this each month, so trying to figure out an easier way to do this month-to-month.

Column A is what I what I would want the result to look like. Just simply entered into a1 =b1 and down to give example, but at the bottom it also shows how it would not add those up either. End result, one column with just the number with no links and able to add them up because I have to sort them out by accounts.

Thanks,
Ant

The values you have in column B are text values, and have a non-breaking space character (with code of 160) at the end. Change your formula in A1 to this:

=SUBSTITUTE(SUBSTITUTE(B1,"$",""),CHAR(160),"")*1

and this will convert them to proper numbers, so that Excel can SUM them correctly.

Hope this helps.

Pete

Thanks, Pete that worked. But is there another way to do it to that column by itself without the formula? So I could just go through the steps or no? Also this would help in explaining to others at work. If not, I appreciate the help.

Or I guess, how did you know of the "char 160" problem?

I suspected that those were being treated as text values, so I tried this in A1:

=B1*1

but that returned an error (#VALUE), so there was something else that was causing it to be a text value. At first I thought it was the $ symbol, so I tried this formula in A1:

=SUBSTITUTE(B1,"$","")*1

That also returned an error, so in a blank cell I put this formula:

=LEN(B1)

which returned 10, although there were only 9 characters that I could see. So, it looked like there was some kind of space in there, and as it wasn't visible at the beginning of the value it must have been at the end. I then put this formula in another blank cell:

=CODE(RIGHT(B1))

which returned 160, and confirmed that there was a non-breaking space character at the end. Thus I had to modify the original formula to get rid of both the $ and the nbsp.

Hope this helps.

Pete