Extracting amounts from text field 1

[UongSaki]

Is there a way to extract dollar amounts from this type of file? I have a text file that when loaded into Excel all the values are in column A called value to extract. I would like the result in column B but I don't know how, help please. Thanks

Value to extract Extracted Vale
110 C14005 1000 :
I Availability control:
E Item 000 C14005 /110 /1000 payment budget for year 2004 exceeded
50009 501001 1000 C50009 100 168,901.00-Original PBgt $(168,901.00)
100 C50009 1000 :
I Annual budget payments 2004 negative
E - C50009 /100: 168,901.00- (USD) $(168,901.00)
E - C50009 /OPERATIONS: 168,900.00- (USD) $(168,901.00)
E - C50009 /PRIMARY: 168,900.00- (USD) $(168,901.00)
50009 502502 1000 C50009 100 90,362.00-Original PBgt $(90,362.00)
100 C50009 1000 :
I Annual budget payments 2004 negative
E - C50009 /100: 90,362.00- (USD) $(90,362.00)
E - C50009 /OPERATIONS: 90,361.00- (USD) $(90,362.00)
E - C50009 /PRIMARY: 90,361.00- (USD) $(90,362.00)
52000 509015 1000 C52000 110 600.00-Original PBgt $(600.00)
110 C52000 1000 :
I Annual budget payments 2004 negative
E - C52000 /110: 600.00- (USD) $(600.00)
E - C52000 /OPERATIONS: 600.00- (USD) $(600.00)
E - C52000 /PRIMARY: 600.00- (USD) $(600.00)

 

[SkipVought]

Hi,

Select Col A

menu item Data/Text to Columns

select delimited

in the OTHER checkbox enter $

Finish



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UongSaki]

Hi Skip,

Thanks for the tip but I cannot find the checkbox any where when issuing Data|Text to Columns only Delimited & Fixed Width. I'm using Excel that come with Office 2000.

Thanks,

Saki

 

[SkipVought]

"select delimited"

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UongSaki]

I've just did, it looks the same.

Thanks,

Saki

 

[SkipVought]

when the DELIMITED window opens, did you enter $ in the OTHER checkbox?

Have you selected you column of input data?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UongSaki]

Yes, I selected the input colunm & also entered the $ in the Other checkbox. I got the same result.

Thanks,

Saki

 

[SkipVought]

And you are ABSOLUTELY sure that none of the data has already been parsed.

This works! Puts the amount after the $ into the next column

Check for data in the adjacent column

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dcompto]

Skip's instructions worked in my Excel 2000. I copied the data from your post into Excel then...

Double-click Column A border to adjust col width
Click the column heading of Column A
Data-Text to Columns
Delimited
Next
Check the box to left of "Other"
Type $ in box to right of "Other"
Finish
Double click Column B border to adjust col width

 

[UongSaki]

Skip, I got exact same data. I must be doing something wrong here. Here are my steps:

1. Select colunm A
2. Data|Text to Colunms
3. Select delimited
4. Check Other Box and enter $
5. Destination cells $B$1

The result in colunm B is the same as in colunm A.

Thanks,

Saki

 

[SkipVought]

THEN...

under that scenerio, your dollar amounts ought to be in column C

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

LOL - You'll have poor old Skip pulling his hair out in a minute, because he knows this really does work

With the steps exactly as you have outlined, you should see the data you want in Col C, UNLESS, you selected 'Do Not Import Column' on the first column of data as you went through the import wizard, in which case it would appear in Col B. Try expanding Col C and look where all the ####s probably are.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[UongSaki]

I'm sorry but the data I posted contained two colunms. The first colunm labeled Value to extract. That colunm has a long string of texts and numbers. The other colunm labeled Extracted value, I manually typed in those mumbers to show the result that I need.

Thanks,

Saki

 

[SkipVought]

OK

First, number the rows of data, cuz we're gonna sort it and will need to put it back into order.

sort the column to parse. notice that all the data of a similar type will be grouped.

select a similar group (only one column -- many rows) Data/text to columns - delimited and enter a colon : for the delimiter

          E - C50009 /100:            168,901.00-  (USD)    
          E - C50009 /OPERATIONS:             90,361.00-  (USD)    
          E - C50009 /OPERATIONS:            168,900.00-  (USD)    
          E - C50009 /PRIMARY:             90,361.00-  (USD)     
          E - C50009 /PRIMARY:            168,900.00-  (USD)

result in second column

            168,901.00-  (USD)     
             90,361.00-  (USD)     
            168,900.00-  (USD)     
             90,361.00-  (USD)     
            168,900.00-  (USD)

Then select this data and delimit on -

The other data looks like this

50009                    501001         1000       C50009           100                                 168,901.00-Original     PBgt     
50009                    502502         1000       C50009           100                                  90,362.00-Original     PBgt     
52000                    509015         1000       C52000           110                                     600.00-Original     PBgt

This time use fixed width to isolate the dollar amount and what follows and follow up with the delimited -






Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UongSaki]

Skip, you are a genius! Thanks, it works.

Again, thank you - Saki