About EasyMorph Tutorials & Examples Web-help

Split text into columns problem - split when data changes from text to numbers

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:

  1. 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"
    

Or,

  1. 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 :slight_smile:

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.

To learn more about EasyMorph visit easymorph.com.