regex - Find every instance of "Average (variable dollar value) $" and remove everything but the dollar valueb

admin管理员组

文章数量:1356332

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.

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.

• regex
• macos
• sublimetext

Share Improve this question Follow edited Mar 28 at 1:32 Matt Lehmer asked Mar 28 at 1:18 Matt LehmerMatt Lehmer 2733 bronze badges 10

• 1 It's certainly possible, as long as your original data doesn't have bad mistakes (like missing dollar symbols, etc.) Please share what you tried and what you thought might work, even if it didn't. It helps to know what you're thinking of, and what level of explanation is required to actually help. Also, do you need something that finds a single value if it's there, or does your actual data or document have many of these in a row, and do you need to extract all of them? – Grismar Commented Mar 28 at 1:20
• Why do you have an added line in the expected output, and is that part of the replacement? – Tim Biegeleisen Commented Mar 28 at 1:25
• The data seems good, very consistent. I mainly just tried to searching with some wildcards to get everything between the word average and the dollar sign just see if I could get that done. I'm pretty new to all this. That sample record is an entire record. All i really need is just the dollar value after the word average. Does that help explain the challenge better? – Matt Lehmer Commented Mar 28 at 1:37
• @TimBiegeleisen. I was just thinking if it's not possible to remove everything besides that dollar value, simply making it easier to extract by adding a carriage return before and after the value. Maybe i'm making this harder than it has to be. – Matt Lehmer Commented Mar 28 at 1:43
• Then is your problem to extract the average values, or to reformat the text as above? – Tim Biegeleisen Commented Mar 28 at 1:44

 |  Show 5 more comments

2 Answers 2

Sorted by: Reset to default 1

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.

Tested in Sublime Text.

FIND:

\*\*Average (\d+\.\d\d)\$\*\*

REPLACE:

Average\n $1\n\$

Regex Demo: https://regex101/r/SYl9PY/1

TEST STRING:

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

RESULT:

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

DESIRED OUTCOME:

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

REGEX NOTES:

• \*\* Match literal "**".
• Average Match literal "Average".
• Match literal space character " ".
• (\d+\.\d\d) Capture group (...). Group 1 is represented by $1 in the replacement string. Matches 1 or more (+) digits \d, followed by a literal dot, \., followed by two digits \d\d.
• \$ Match literal "$".
• \*\* Match literal "**".

REPLACEMENT STRING NOTES:

• Average Literal "Average"
• \n Newline
• Literal space character " "
• $1 Characters stored in group 1
• \n Newline
• \$ Literal "$"

本文标签：