how to extract currency from a string of text
similar to a bank statement
07-22 Point Of Sale Withdrawal MARTYS FI 61 MAIN13.99- SHOPKINTON MAUS
07-22 Point Of Sale Withdrawal PAN THAI44.38- RESTAURANTHOPKINTON MAUS
07-22 Point Of Sale Withdrawal ALS STATE STREET10.00- CABOSTON MAUS
07-22 External Deposit Resilience03WBZk C:42320H:03WBZk -35.00DIR DEP
I want to be able to extract the bold numbers nnnn.nn
Answers
-
Paul Newcome ✭✭✭✭✭✭This may need to be a manual process unless you (hopefully) have a slight typo on the last entry where the amount comes after the hyphen as opposed to before the hyphen as it is in the other entries.
If it is not a typo, will that only ever happen when the last bit is "DIR DEP"?
Basically... We need to find some kind of logic that is the same across every entry of similar type (such as POS vs Dir Dep) or across every entry regardless of type (this would be ideal).
thinkspi.com
Help Article Resources
Categories
Check out theFormula Handbook template!
Hi @John Littler<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107942,"type":"question","name":"How do I return DISTINCT values from another sheet based on specific dates within that sheet?","excerpt":"Hello, I have a need to return a Distinct Item ID from another sheet based on a date range from another date column within that same external sheet. The formula below works; however, once I run out of Distinct item IDs that meet my date criteria it continues to return results that fall outside that date range. Current…","snippet":"Hello, I have a need to return a Distinct Item ID from another sheet based on a date range from another date column within that same external sheet. The formula below works;…","categoryID":322,"dateInserted":"2023-07-22T00:19:14+00:00","dateUpdated":null,"dateLastComment":"2023-07-24T14:28:01+00:00","insertUserID":123219,"insertUser":{"userID":123219,"name":"Matt Foss","url":"https:\/\/community.smartsheet.com\/profile\/Matt%20Foss","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bCFRG7SgS_o!w6Nev-KRhHQ!jkKTibQm-FP","dateLastActive":"2023-07-24T14:20:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-24T20:48:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3380195835,"url":"https:\/\/community.smartsheet.com\/discussion\/107942\/how-do-i-return-distinct-values-from-another-sheet-based-on-specific-dates-within-that-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107942\/how-do-i-return-distinct-values-from-another-sheet-based-on-specific-dates-within-that-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107942,"commentID":386638,"name":"Re: How do I return DISTINCT values from another sheet based on specific dates within that sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386638#Comment_386638","dateInserted":"2023-07-24T14:28:01+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-24T20:48:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/74OMVUEY31TO\/example.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Example.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-24T14:20:29+00:00","dateAnswered":"2023-07-22T02:23:22+00:00","acceptedAnswers":[{"commentID":386531,"body":" I think this is what you are trying to accomplish. I assume you are dragging the formula down, as this will not work as a column formula due to the absolute references. There were a few issues with the formula you posted, maybe there were some typos when transferring it to your post? <\/p> =IFERROR(INDEX(DISTINCT(COLLECT({Item ID}, {Create Date}, >= $[Start Date]$1, {Create Date}, <= $[End Date]$1)), [Row ID]@row), \"//m.santa-greenland.com/community/discussion/84833/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
=IF(OR(Idea34 = \"//m.santa-greenland.com/community/discussion/84833/\", Idea58 = \"//m.santa-greenland.com/community/discussion/84833/\"), \"//m.santa-greenland.com/community/discussion/84833/\", IF(OR(Idea34 < 0, Idea58 < 0), \"Red\", \"Green\"))\n<\/pre>
