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?
Hello @Mr.H and welcome to the Community!
There are few ways how to separate names and numbers in this case:
Use the keepchars(), removechars() functions to keep/remove only digits. For instance:
removechars("Doe, John 12345678", "0123456789") returns "Doe, John "
keepchars("Doe, John 12345678", "0123456789") returns "12345678"
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.
See the example below.
separate-text.morph (3.3 KB)
Thanks a lot, @dgudkov, this solved the problem perfectly.
You’re welcome, @Mr.H
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.