regexrep for the neophyte
Show older comments
How to write expression to find and convert financial strings written with comma separator to be able to parse numerically?
Example stirng is something like:
recipients={'John Doe $200, Freddy Flint $132.40 SP19; Mary Lamb $1,423.00-SP19, Joe Blow $1,200'};
which is a list of student scholarship awards entered, unfortunately, free-style in a remarks field in an Excel spreadsheet. I need to parse by student and extract each.
I managed to find/return the location/token of the amounts containing the punctuation, but I'm illiterate with regexp and haven't figured out how to locate the comma then within that substring to remove it.
K>> regexp(recipients,'([$]\d+[,]\d+)','match')
ans =
1×1 cell array
{1×2 cell}
K>> ans{:}
ans =
1×2 cell array
{'$1,423'} {'$1,200'}
K>>
As can be seen, the original text also has "issues" in that the separator between students isn't consistent--it may be a comma or semicolon, not sure what else I'll find as I progress.
For context, this is the next step past the previous Q? of piecing back together disparate databases/spreadsheets...now that I can compare the award to the billing by account, I can find coding or other errors--but need to be able compare the details.
5 Comments
Walter Roberson
on 20 Apr 2019
regexprep(receipts, ',', '') perhaps?
the cyclist
on 20 Apr 2019
Maybe try making this into a Cody problem, too. :-)
Accepted Answer
match = regexp(recipients,'(?<name>[a-zA-Z]+(?:\s*[a-zA-Z]+)*)\s?\$?(?<sum>\d+(?:[\.,]\d+)*)[^\w,;]*(?<time>[a-zA-Z]+\d+)?','names');
Will return a struct array with like that:
ans = 1x4 struct array with fields:
name % recipient name
sum % scholarship amount
time % time string
Time is not mandatory so it can be an empty char vector
sum is convertible to numbers using str2double even though it contains the commas
If you want clarifications for the regex pattern I'll gladly add them
12 Comments
Note that this will also match "numbers" such as 1.2.3.4.5.6.7 or 123456,7,8,9, because it does not require any particular number of digits in each digit-group, and the decimal radix/comma can repeat any number of times. This might be an advantage or a disadvantage, depending on your source data.
TADA
on 21 Apr 2019
Yes, that can be fixed if necessary.
If in some Chilean University a student receives 1,000,000 pesos that regex would still work though
It depends on how much vallidation is required
dpb
on 21 Apr 2019
TADA
on 21 Apr 2019
Sounds like a serious headache U_U
1. adding a question mark right after the sum named group like that
(?<sum>blah balh blah)?
should solve that (like with the time named group), however, it might cause the whole expression to detect false negative time strings...
2. If the time string is different and there is no $ amount it's going to be more complicated... Im going to have to think a bit about it
If you can throw in a few more of these problematic strings it might help
dpb
on 21 Apr 2019
dpb
on 21 Apr 2019
TADA
on 22 Apr 2019
That's the problem usually with unstructured input systems.... Text input is so hard to analyze
I will try to have a look at the data you posted when I can
Unfortunately I know next to nothing about fuzzy logic, so I'd suggest opening a new question about that to attract the attention of Matlab answers "big guns"
dpb
on 22 Apr 2019
TADA
on 22 Apr 2019
Cheers then! =)
dpb
on 22 Apr 2019
More Answers (0)
Categories
Find more on Matrix Indexing in Help Center and File Exchange
