extracting data

R

rockspeed

Extracting data from a single cell
Hello

I am using Excel 2002, I have 4000+ records each cell has words date
and cost in them. I would like to extract the cost and the tim
elements from the cells can any one help. Am I in the right forum
 
P

Peo Sjoblom

Assuming the data starts in A2, in E2 put

=TRIM(LEFT(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2)))+1,1))
,0)))

entered with ctrl + shift & enter, copy down to E4000 something

in F2 put

=TRIM(LEFT(SUBSTITUTE(A2,E2,""),FIND("^^",SUBSTITUTE(SUBSTITUTE(A2,E2,""),"/
","^^",2))+2))

copy down

in G2

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,E2,""),F2,""))

copy down

now select E2:G4000 (or whatever the size is) and copy it, then
do edit>paste special as values in place..

To make the amounts and dates numeric, copy an empty cell,
select E2:G4000 and do edit>paste special and check add.
Select the dates column and format as dates and the amounts and format as
currency.

If there is a specific pattern you might be able to do this by using
data>text to columns
 
B

BrianB

Have a look at the =LEFT(), =RIGHT(), and =MID()
worksheet functions in Excel Help.

If you want further help you will need to show some sample data
 
R

rockspeed

Hi BrianB

the formula worked in as much as it extracted information out of 1
cell, I need a calculation that will extract data out of multiple cells
all at once. This is a sample of the data I am working with. Each Line
is in one cell and i need to extract the "0.418" type number out of
4000 cells to Insert into another colum so that I can add up the
values. Can you help,

LOCAL CALL 16 MAY 03 12:40 12:35 0.418
LOCAL CALL 16 MAY 03 14:22 3:30 0.127
LOCAL CALL 16 MAY 03 14:54 1:52 0.075
LOCAL CALL16 MAY 03 14:59 12:20 0.410

LOCAL CALL 16 MAY 03 12:40 12:35 0.418
LOCAL CALL 16 MAY 03 12:40 12:35 0.418
LOCAL CALL 16 MAY 03 12:40 12:35 0.418

Thanks for your time
 
Top