0

Splitting data or trimming where a space is

Could someone please tell me how to remove all text prior to a space? 

 

I have a column "Colour" and the data is presented as "Clear 3mm", "Clear 4.5mm", " Lemon Grass 3mm" etc 

 

I'd like to be able to split it after the last space, so that I can get a column for "Clear" and one for "4.5mm" for example. 

 

It could be possible to split the data somehow, or do two columns with the same data, one trims after last space, the other trims before ? I thought about using trim, but the data various in how many characters too much. 

5 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 3 yrs ago
    • Reported - view

    Hi Aaron,

     

    In plain text (to copy/paste):

    for s in select 'Splitting text and number' do
    let i := 0;
    let f := -1;
    while f = -1 do
    f := index(s.FullText, text(i));
    i := i + 1
    end;
    s.(Color := substr(s.FullText, 0, f));
    s.(Dimention := substr(s.FullText, f))
    end

    Steven

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Another way to solve this class of problems is to apply some magic, er, I mean, some regular expressions. The two following functions, entered in two Formula fields, will yield the characters before and after the last (group of) space(s):

    replacex(Colour, "(^.*?) +[^ ]+$", "", "$1")
    replacex(Colour, "^.*? +([^ ]+$)", "", "$1")

    • Ninox partner
    • RoSoft_Steven.1
    • 3 yrs ago
    • Reported - view

    I knew there was a simpler alternative with regex, but I don't know enough about that. It works very well. Well done and thanks Alain. 

    • Producer, Manager
    • Jan_Inge_Iversen
    • 2 yrs ago
    • Reported - view

    Is it possible to remove all text before or/and after a space?
    Can I for example split the Location to several address fields in Ninox?

    • Producer, Manager
    • Jan_Inge_Iversen
    • 2 yrs ago
    • Reported - view
    Alain Fontaine said:
    replacex(Colour, "(^.*?) +[^ ]+$", "", "$1")replacex(Colour, "^.*? +([^ ]+$)", "", "$1")

     I tried this one, and it worked. But how to change the number of spaces, so I can pick up words in between?
    I am trying to split the address in a Location filed to several formula fields who represent the address fields like Street, Zip, City etc.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 5Replies
  • 413Views
  • 3 Following