extracting data

  • Thread starter Thread starter rockspeed
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top