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.
Best Answer