Extract the first word from a cell

A Rose

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))

Andrée Starå

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!

Andrée Starå

This is a great resource.

View invisible/whitespace characters (invisible-characters.com)

123U+0009thisU+0020andU+0020that

U+0009 CHARACTER TABULATION (invisible-characters.com)

L_123

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

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

L_123

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

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

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

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

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

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

give this a try.

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

A Rose

It also gives me the first 2 words.

(FYI See my last 2 comments updated)

A Rose

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

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

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

123        this and that

Here it is...!

A Rose

Can you maybe VLOOKUP the space in your Smartsheet?

Thanks,

A Rose

123this and that.docx

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

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