Hi all, I'd like to ask for your help with a problem I am trying to solve with splitting multiple delimiters within a string.
First of all, a bit of backstory: I work as a teacher in a prison running day courses for up to 18 inmates at a time. Recently, my employer has decided to increase my fellow teacher's and my workload by several hours per week by adding a bunch of different forms (all containing much the same information) to satisfy various department head's preference for a specific layout etc. Of course they want them all to be filled by hand (oh did I mention we get regular shit-o-gram emails when there are errors with the data? I can't understand why that is happening...).
I have absolutely no interest in entertaining such an obnoxious system and have thus decided to use excel to automatically create all of these different documents from the raw data string found on the prison muster (essentially a list of all inmates two identity numbers, last name, first name, middle name). This prison muster is supplied to us in a text document format only.
I have tried using text to columns but this command only seems to work when the information to be split is already in place (I want to create a form that allows you to clear yesterday's names and paste in the next day's without having to run that command as some of our teachers are not great with computers and this could very well overwhelm a couple of them). I have found quite a few articles online with formulas achieving exactly what I am after, however they only seem to work for a maximum of 3 delimiters, whereas I need 4 and 5.
See below for a screenshot of what I am trying to achieve (names and prison numbers have been seriously modified to protect privacy).
I'd prefer not to have to spend a significant number of hours learning VBA as I have no knowledge in this area whatsoever (the fact that I had to google VBA to find out what it was gives you some idea of my ineptitude for programming).
Any help or advice would be very much appreciated, thanks in advance,
Axel
First of all, a bit of backstory: I work as a teacher in a prison running day courses for up to 18 inmates at a time. Recently, my employer has decided to increase my fellow teacher's and my workload by several hours per week by adding a bunch of different forms (all containing much the same information) to satisfy various department head's preference for a specific layout etc. Of course they want them all to be filled by hand (oh did I mention we get regular shit-o-gram emails when there are errors with the data? I can't understand why that is happening...).
I have absolutely no interest in entertaining such an obnoxious system and have thus decided to use excel to automatically create all of these different documents from the raw data string found on the prison muster (essentially a list of all inmates two identity numbers, last name, first name, middle name). This prison muster is supplied to us in a text document format only.
I have tried using text to columns but this command only seems to work when the information to be split is already in place (I want to create a form that allows you to clear yesterday's names and paste in the next day's without having to run that command as some of our teachers are not great with computers and this could very well overwhelm a couple of them). I have found quite a few articles online with formulas achieving exactly what I am after, however they only seem to work for a maximum of 3 delimiters, whereas I need 4 and 5.
See below for a screenshot of what I am trying to achieve (names and prison numbers have been seriously modified to protect privacy).
I'd prefer not to have to spend a significant number of hours learning VBA as I have no knowledge in this area whatsoever (the fact that I had to google VBA to find out what it was gives you some idea of my ineptitude for programming).
Any help or advice would be very much appreciated, thanks in advance,
Axel