MATLAB Answers

dpb
0

regexrep for the neophyte

Asked by dpb
on 20 Apr 2019
Latest activity Edited by dpb
on 24 Apr 2019
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

the cyclist
on 20 Apr 2019
Maybe try making this into a Cody problem, too. :-)
dpb
on 21 Apr 2019
Not sure what that is, Cyclist??? I've not poked around any of the rest of the site much at all...
ADDENDUM: Oh. I'd forgotten about it...do know what it is, never played. I think I answered one or two easy problems "way back when" ... I don't have the needed 200 points accumulated to post, though...
dpb
on 24 Apr 2019
OK, if anybody is still listening/watching -- I've found another general pattern that occurs often enough it would be big help to handle --
{'Mare Hartman (Pres. Tuit/Bks) $1,374 AY18/19; Mark Lester $1,376 SP19'}
The pattern adds a parenthetical comment and the semester time code is academic year of YR1FA/YR2SP. Present gets confused by the extra words/strings:
>> mtch=regexp(char(tAll.Recipient(lib)),rexp,'names')
mtch =
1×2 struct array with fields:
name
sum
time
>> struct2table(mtch)
ans =
2×3 table
name sum time
________________ _______ ______
'AY' '18' ''
'Mark Lester' '1,376' 'SP19'
>>
It would be ideal if could return an additional optional 'notes' variable containing the text in the parentheses and somehow manage to not split the semester string and interpret it as the $$ amount...
I've made a few feeble attempts but only managed to break what did work and not get further... :(

Sign in to comment.

1 Answer

TADA
Answer by TADA
on 21 Apr 2019
Edited by TADA
on 21 Apr 2019
 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

dpb
on 22 Apr 2019
Anything further would be gravy... :)
I've gotten a first pass at matching names for the first large block of awards to a given fund that doesn't match the billing sheet totals -- and, by just a couple of machinations all the names match excepting two...one that the first/last names were reversed --an Owen Martin became Marty Owen and a second that is just misspelled...both of which would be where some general matching logic could probably have discovered. But, being able to spit them out as not matching is also a win outside having to search the comment field by hand/eye.
For it, there are the same number of entries in the formula, and I can match all but one with the billing but the numbers for the individual recipients don't match -- again, having that in a table by Fund/Student is a win even if it isn't perfect parsing...
I may be able to get something working well enough tomorrow to actually be able believe can get the problems uncovered and solved...before getting this far I wasn't so sure it was possible in less than "months" time frame which would be past the time the books have to be closed and errors would become permanent for evermore at that point, at least without a tremendous amount of additional forensic accounting.
TADA
on 22 Apr 2019
Cheers then! =)
dpb
on 22 Apr 2019
Not that I wouldn't find use for any further improvements... :)
Probably it's quicker despite the pain to just edit the remarks to make them more nearly regular and then reparse.
My plan of attack is to find the donor funds that show a mismatch and work through them first, then expand to more thoroughly vet the remainder that could have totals that agree but just by accidental cancellation of errors and keep expanding the thoroughness until either get completed or simply do run out of time...

Sign in to comment.