breaking single cell into individual cells so i can find

[moogle]

Hi guys, The figures shown below are a good example of what I am working with. I am trying to column these prices so that I can find the average selling price quickly again and again. They are taken directly from a website and therefore when I copy them into a excel document they all lump sum into a single cell, like this:

$96 $171 $122 $61 $94 $150 $180 $88 $415 $22 $150 $10.50 $899 $51 $41 $109 $157 $149 $90 $650 $151 $201 $117 $102 $107 $51.70 $172 $156 $168 $42 $181 $75 $164 $167 $46 $25 $16 $21 $50 $100 $3.50 $195 $31 $50 $5.50 $46 $100 $40 $349 $10

Each price needs to be in its own cell otherwise it isn't much use for calculation's. Is there a quick and easy answer to this problem?

(Using excel 2000)

[solnajeff]

Hi

To split the data you can use the Data/text to columns feature and select space as the delimiter.

If you have a great deal of data to enter you could try the following. Browsers deal with copying and pasting a little differently and Explorer will usually give the result that you experienced. Often using Firefox the same data copied and pasted will arrive correctly in Excel, it may be worth trying.

Regards

Jeff

[MickG]

Y ou could try this:-
If you know how to, you can copy this bit of code into a Command Button.
Failing that Write an empty Macro and copy the code below into it, between
the words Sub Macro?? & End Sub.
The code refers to your (One cell) list of information in Range("B1) and the resulting output
of individual numbers, prefixed by "$" will be in column "A"
Amend these Ranges addresses to suit your needs

Dim list, c, v, all, CL
CL = Range("b1").Value 'Amend Range to suit
c = 1
For v = 1 To Len(CL)
If Not Mid(CL, v, 1) = " " Then
all = all & Mid(CL, v, 1)
Else
Cells(c, "A").Value = all 'Amend Range "A" to suit
c = c + 1
all = ""
End If
Next

You will need to Format Column "A" as currency "$" to One decimal Place
Regards Mick G