Forum Discussion
extracting text after the last : in a text field
I have a series of account number sin excel like
| 030.40 OVERHEADS / EXPENSES:SELLING / MARKETING OVERHEADS:EMPLOYEE EXPENSES:Superannuation |
| 030.40 OVERHEADS / EXPENSES:SELLING / MARKETING OVERHEADS:EMPLOYEE EXPENSES:Vehicle Allowance |
| 030.40 OVERHEADS / EXPENSES:SELLING / MARKETING OVERHEADS:EMPLOYEE EXPENSES:Leave adjustment - annual & LSL |
| 030.40 OVERHEADS / EXPENSES:SELLING / MARKETING OVERHEADS:EMPLOYEE EXPENSES:Fringe Benefits Tax |
| 030.40 OVERHEADS / EXPENSES:SELLING / MARKETING OVERHEADS:EMPLOYEE EXPENSES:Payroll tax |
030.40 OVERHEADS / EXPENSES:SELLING / MARKETING OVERHEADS:EMPLOYEE EXPENSES:Salary charge - Bill Adamson I need to extract the text to the right of the LAST : for each row The complication is that there is a variable number of colons, and their position varies. I use Len to find the length of the text, find to locate the position of the first : then use right to extract all the text after the : I then value copy the result back onto the source text, and repeat until an error (because there is no :) How do I write VBA to repeat this until the error and then move down to the next row and repeat again?
|
2 Replies
You "count" the occurrences of ":" and get the last one. Here's the first example I found on google: https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string
thank you Vasil
that is a perfect answer
