How to use cell data in formula after splittling cells

  • Thread starter Thread starter tazzi
  • Start date Start date
T

tazzi

Hey,

I pasted a text document into excel which i wanted in three columns
date, price and volume. I successfully split these up using "conver
text to table".
What I want to do now is use the price data in a formula, but it keep
coming up with "VALUE" error.
Can anyone tell me what I have done wrong and how I can fix it?

Thanks heaps!!
 
Hi
sounds like your values are actually stored as text. Try the following:
- select an empty cell and copy this cell
- select your price and volumn data
- goto 'Edit - Paste Special' and choose the action 'Add'

now your formula should work
 
hey frank,

im sorry i dont understand what you mean. i agree that excel thinks th
prices are just text. but i dont understand how to fix it. could yo
please explain it another way.

ta
 
Hi
o.k. lets try it again :-)
- Assumption: your values are in cells A1:A8
- select this range A1:A8, goto the menu 'Format - cells' and choose
the cell format 'General'
- select ANY empty cell (e.g. cell B1 if this is an empty cell)
- copy this cell. e.g. just hit CTRL+C
- now select your values which seems to be numbers but are stored as
'Text'. That is according to my assumption just highligh cells A1:A8
- now goto the menu 'Edit - Paste Special'
- in this dialog select the action 'Add') (bottom of this dialo) and
hit 'OK'

This should convert your 'text' numbers to real numbers. If you have
problems following this procedure please state exactly what does not
work for you
 
when ive finished doing what u suggested nothing changes at all

the "text" is already in 'general' format. i understand this part.

but why do i copy an empty cell?

when i paste special - add nothing gets pasted. is ti supposed to?

i still cant use the data without a VALUE error coming up

thanks for your time frank
 
Copying an empty cell and paste special value combine to make excel reevaluate
your value in that cell. If it looks close enough to be a number, then excel
will convert it to a number.

But if there's something else in that cell (say some non-breaking spaces), then
this technique won't work.

Non-breaking spaces (hex 160) usually come from copying and pasting from a web
page. Is that where your data originated?

If yes, David McRitchie has a macro that can clean up this stuff:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

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

If you're not sure what is in the cell, Chip Pearson has a very nice addin that
can help identify each character:

http://www.cpearson.com/excel/CellView.htm
 
Back
Top