Copy Partial Cell Contents in Excel ?

[MacDubhgal]

Hi folks!

I'm trying to figure out how to write a Macro in Excel that I can us
to copy only a portion of data in a cell and paste that partial dat
into an adjacent cell. Specifically, I am using Excel 2000 and tring t
copy only the final dollar amount from entries such as the one below:

1@ $5.99 = $5.99

What I want is a pure dollar amount in the adjacent column so that
can run totals of amounts spent. What I don't want is to have to cop
and paste every entry manually. Can anyone help me with this

 

[Earl Kiosterud]

MadDubhgal,

That cell should be in three cells (three columns if there are more of them)
for just about anything you'd want to do with the data in a spreadsheet.
You can do that with Data - text to columns, or with formulas. If you're
interested, post back with details about the sheet.

 

[MacDubhgal]

OK... I'm confused. All I'm looking for is a function or macro tha
will allow me to copy the final dollar amount listed in the cell abov
to be copied into another cell so that I can manipulate it as a pur
numerical operator. The data mentioned above exists all within a singl
cell. Or am I missing what you're saying here?

It's a single column currently, with data in the format explaine
above, which is useless to me. I need a dollar amount out of that data
but do not know how best to automatically extract it from the data as i
is currently presented in the sheet

 

[Earl Kiosterud]

Mac,

The 1, the $5.99, and the second $5.99 should likely have been put into
separate cells in the first place. The second 5.99 should be a formula that
multiplies the first two, e.g.:
=A2 * B2

My recommendation to separate them into columns now.

To extract the quantity, use:
=VALUE(LEFT(A2,SEARCH(" ",A2)-1))
or possibly:
=VALUE(LEFT(A2,SEARCH("@",A2)-1))

To extract the unit price, use:
=VALUE(LEFT(MID(A2,SEARCH("@",A2)+1,999),SEARCH("=",MID(A2,SEARCH("@",A2)+1,
999))-1))

To extract the final dollar amount use:
= A2 * B2
Or if you didn't do the first two, use:
=VALUE(MID(A2,SEARCH("=",A2)+1,999))

 

[Jim May]

if "1@ $5.99 = $5.99" (without the quotes) is in cell A1
in B1 enter =VALUE(RIGHT(A1,LEN(A1)-FIND("=",A1)-2))
and copy down.
HTH