Hi. I have an Excel-file with a table with employee data that I want to upload to a mySQL database. The table, which is exported from the HR system, has one column that contains both names and employee numbers. The problem is, to make use of this data I need to have the employee number separated to a new column. The employee number consist of 8-10 numbers. In the Excel-file, the data looks like this:
[Last name], [First name] [middle name] [employee number]
For example: Doe, John 12345678
or: Smith, Alex Bob 1234567899
I have previously been able to separate the employee number in Excel, using Power Query and splitting the column when it changes from text to numbers. But I cannot fint anything similar in EasyMorph. I have tried using the “split delimited text into columns”, but I don’t think it is suitable for this problem But I am new to this application, so I might have overlooked something.
So, anyone has any suggestions on how to solve this problem?
removechars("Doe, John 12345678", "0123456789") returns "Doe, John "
keepchars("Doe, John 12345678", "0123456789") returns "12345678"
Or,
Use the keepafter() and keepbefore() functions, to keep parts of text before or after a separator (such as space). The functions have recently received a new syntax (the help articles are not updated yet) that allows specifying a separator entry other than the first from the beginning. For instance:
keepbefore("Doe, John 12345678", " ", -1) returns "Doe, John"
keepafter("Doe, John 12345678", " ", -1) returns "12345678"
The negative 3rd argument in this case indicates the 1st separator entry from the end.
Note that if you used the removechars() function, the name will have a trailing space. It can be removed using the trim() function. The example attached in my answer already does that.