Cut/Past Currency is Text

  • Thread starter Thread starter RzB
  • Start date Start date
R

RzB

I have cut and pasted some data into excel from a web page.

It sorts out the columns just fine. However one of the columns
is a list of currencies but Excel it treating it like text. Each cell
has a leading £ and a couple of trailing blanks. As it is text I am
unable to sum etc. Is there some way to simply overcome this...

Many thanks,
Roy
 
Hi Roy!

Select the column of data and use:

Data > Text to Columns
Check fixed width
Next
Insert a divide after the currency symbol
Next
Finish

You can then delete the first column.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Oooo - That's interesting - Didn't know about that feature...
However - it's not quite fixed the problem... Yes, it's
got rid of the currency symbol but I still have the two
trailing blanks. I can't do the same trick with the blanks
because some of the numbers are longer than others.
I tried right justifying first but that didn't help...
Any other thoughts?
Many Thanks,
Roy
 
Hi RzB!

Select an Empty cell
Copy
Select the entries you have
Edit > Paste Special > Add > OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman...
Many thanks for your assistance with this..
That does not work for me... Hmmm
Nothing shows up in the copied to cells.
What could I be doing wrong.... Hmm

Why would this work? Are we not trying
to add text? Why would that convert the
text to numbers? Were you expecting
some implicit conversion to happen?

The only way I have fixed this so far
is to copy the col to Word then use
Edit/Replace to do away with the blanks.
Then copy back to Excel...
A bit long winded!!! Still - better than
editing each cell!

Many thanks,
Roy
 
Hi RzB

I was referring to the result that you got after doing the stripping
of the currency symbol where you said it was OK but you got a number
with two trailing blanks.

What adding 0 does is coerce these text numbers to be numbers.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman,
Perhaps I'm not explaining myself very clearly or
I'm not understanding what you are telling and am
doing something wrong....

Let me try taking an example of just one cell...

When I paste from the web the cell contains "£12.34 "
That is eight characters... £, 1, 2, period, 3, 4, space, space

I do the trick you suggested using Data/Text to Cols and
removed the £ sign, That leaves seven characters in the cell.

Now I try to use the special paste/add but it does not convert
the seven text chars to numeric data. The result of the
addition is nothing.

Is it perhaps that the data is not text? Could it be RTF, HTML,
or something?

Her is an example of the data - only three cells worth...

http://www.oofus.com/pix/ExcelTest.zip

What am I doing wrong? An options setting perhaps?

Many thanks for your help,
Roy
 
Hi RzB!

Stubborn little b....s aren't they?

Use the formula:
=--LEFT(B1,LEN(B1)-2)
Then select the results and use
Copy
Edit > Paste Special > Values > OK

Now format as currency (perhaps selecting the UK pound symbol from the
drop down).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Roy!

The problem was that the spaces after your numbers were not the normal
character code 32 but was code 160.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Roy!

If you have different numbers of these spaces after the numbers you
can use:

=--SUBSTITUTE(B1,CHAR(160),"")
This strips out the code 160 characters and leaves a number (that's
what the -- does)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top