way to split column name into first and last

meadow_ryan
meadow_ryan ⚪️
in Data Platform & Data Science

Hello,

 

Is there a way to split a field if I have into 2? I have a field name, for example Bob Smith, can I split this into 2 so having 'Bob' and 'Smith' for first name and last name

Best Answers

  • Valiant
    Valiant 🔵
    Answer ✓

    Here you go:

     

    First Name:

    LEFT(`name`, (INSTR(`name`, ' ')-1))

    Last Name:

    RIGHT(`name`, LENGTH(`name`)-INSTR(`name`, ' '))

    Hope that helps,
    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • Nick_Bertz
    Nick_Bertz 🟡
    Answer ✓

    Hi,

     

    In Magic ETL, in the Edit Columns group of actions, you'll find 'Split Column' you can use this action to take a full name, and split it into a first and last name.  (See image)

     

    Hope that helps!

     

    Name Split.jpg

Answers

  • meadow_ryan
    meadow_ryan ⚪️

    that worked, thank you

  • user12218
    user12218

    yes , it worked

  • user16290
    user16290

    Hi Nick, I'm trying to split my column similarly however the data in the original column is formatted as, "Stretch Wrap & Strapping - Copy". I want to split this column such that the "Stretch Wrap & Strapping" is in one column and the other column reads, "Copy". I tried making my custom deliminator, " - " however the split column isnt working... any ideas on how to trouble shoot? Thanks!

Categories