regexmacossublimetext

Find every instance of "Average (variable dollar value) $" and remove everything but the dollar valuebefore and after it


I have a 1000 records where each record looks like the below sample. I need to only keep the first dollar value listed after the word average.
Sample Record:

1 1 Antique Railroad Luggage Wagon - Restored
Antique railroad / train luggage cart. Used
on the Saluda Grade. Private Seller 0.00 **Average 925.00$** 925.00$ 925.00$ HOB Contents 64.75$ 989.75$ 0.00 50.00% 50.00% 494.88$ 742.31$ 494.88$ 494.87$ Yes No

I want to search for the first (dollar) value after the word "Average" and before "$"

And i'm hoping to end up with only the dollar value 925.00

It would be ok if I just ended up with a carriage return after the word "Average" and another before the "$" sign to have it look like this:

1 1 Antique Railroad Luggage Wagon - Restored
Antique railroad / train luggage cart. Used
on the Saluda Grade. Private Seller 0.00 Average
 925.00
$ 925.00$ 925.00$ HOB Contents 64.75$ 989.75$ 0.00 50.00% 50.00% 494.88$ 742.31$ 494.88$ 494.87$ Yes No

I've tried a few things, but nothing successfully. Not even sure it's possible.


Solution

  • You may try the following find and replace, in regex mode:

    Find:    \bAverage (\d+(?:\.\d+)?\$)
    Replace: \n$1\n
    

    This will result in an output where every average dollar amount appears in its own separate line. As I alluded in the comments above, a cleaner option would be to use some scripting language to do a regex find all matches.