Extract the first word from a cell

Options

Hi,

In 1 column I have a string that has 1 word then 2 or more words after that, how do I extract from the column the first word only?

In excel I'd use the following

=TRIM(LEFT(A2, FIND("^",SUBSTITUTE(A2, " ", "^",1)&"^")))

In Smartsheet there's no TRIM..

The below formula returns the value of the first and second word,

=LEFT([Column Test]@row, (FIND(" ", [Column Test]@row) - 0))

Best Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @A Rose @L_123

    I hope you're well and safe!

    Wohoo! I think I solved it!

    Try something like this.

    =LEFT(ColumnName@row, (FIND(UNICHAR(9), ColumnName@row)))

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    You can use the one I gave you earlier, just switch the 10 to a 9

    =LEFT([Column Test]@row, FIND(" ", substitute([Column Test]@row,CHAR(9)," ")) - 1

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/16/22 Answer ✓
    Options

    It's a coding/encoding issue. So when you see characters in a digital environment, the environment doesn't typically see the characters themselves, they see an encoded representation of the character. Different datasets can be encoded with different rules and schemes, though a lot of them are very similar to each other. Common encoding schemes would be ANSI, UTF-8, UTF-16.

    In these coding schemes, something visually represented one way, can actually have different meanings in the background to the software. Space is one that is used very often. A good example of this functionality would be the initial one that I suggested might be your problem char(10). char(10) or carriage return is a version of a space, that also tells the software to put the next information on the next line of data. If you don't have this functionality allowed (i.e. wrap text), it will simply appear as a space.

    So the spaces have some added background functionality through the encoding(intended or not, and used or not) in the initial software, that isn't being used by smartsheet, or being allowed to be used by smartsheet by some formatting setting.

    If you really want to figure out what the functionality is, you can look into char(9) or more commonly U0009 (thanks @Andrée Starå) and see what it is supposed to do. It could be something as simple as representing a single space as 5

«13

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/15/22
    Options

    somewhat confused that

    =LEFT([Column Test]@row, (FIND(" ", [Column Test]@row) - 0))

    is bringing back a second word. It's technically wrong as the -0 should be a -1, but it shouldn't bring back a whole second word unless they are a single character...

    =LEFT([Column Test]@row, FIND(" ", [Column Test]@row) - 1

  • A Rose
    A Rose ✭✭✭✭✭
    edited 08/15/22
    Options

    Hi,

    Thanks for that,

    After double checking, when typing it in, It is working, the issue is when I copy the data from a webpage that has a space it sometimes only recognizes the space after the second word...!

    Any way to prevent that?

    Thanks,

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    This won't solve your problem, but might give us some insight into what smartsheet is seeing. Try a substitute returning a special character in the place of the space and see what the program returns.


    =SUBSTITUTE([Column Test]@row, " ", "*")

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Looks like it doesn't recognize the space after the first word as a space, sometimes when copying data from a webpage the system copies a space as part of the first and second word.. like when you back space you'll see a wide space but it's 1 character and you only press 1 time backspace...

    It returns: first-word second-word*third-word

  • A Rose
    A Rose ✭✭✭✭✭
    edited 08/15/22
    Options

    When I press control and enter - to enter the second word on a new row - I'd get the same result. it will not recognize it as a space.

    I found the following on the web referring to different types of spaces, how can I prevent it or change it in the formula?

    https://en.wikipedia.org/wiki/Whitespace_character#Unicode

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Tagging some experts who might know more...

    @Andrée Starå @Paul Newcome

    Please see if you can help when you get a chance.

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    give this a try.

    =LEFT([Column Test]@row, FIND(" ", substitute([Column Test]@row,CHAR(10)," ")) - 1

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    It also gives me the first 2 words.

    (FYI See my last 2 comments updated)

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    I was able to copy the space to word so I can show you an example of how it looks like when highlighted and regular.

    see below,

    When backspacing and spacing again it's the below,


  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    So my last formula was to correct a page break space, which is the most common other type of space, depending on the encoding of the text. The formula will work, we just need to find the correct type of space, and change the integer in the char() to reflect the different type of space. 2 ways I think you can find what type of space it is are

    1: You could try to brute force it, and use all the different types of spaces you can find

    2: You could convert it to hexadecimal and read the hexadecimal

    All unique special characters available are on this smartsheet I made below.

    https://publish.smartsheet.com/f42a4f445a41400dbefa9848360f1a8e

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    can you copy and paste just the text, so I can potentially try to figure out what the format of the space is?

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    123        this and that

    Here it is...!

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Can you maybe VLOOKUP the space in your Smartsheet?

    Thanks,

  • A Rose
    A Rose ✭✭✭✭✭
    edited 08/15/22
    Options


    After checking what I copied it seems that it copies multiple spaces instead of what I have in word, instead attaching the word file!

    *(Updated file attached to triple check it's the same character on the first row..)

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/15/22
    Options

    Yeah, it converted it to 8 spaces. Can you hide any confidential information and share the sheet to me so I can test?

    *Corrected, I saw you attached the document

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!