Extract the first word from a cell
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
-
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!
-
This is a great resource.
View invisible/whitespace characters (invisible-characters.com)
-
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
-
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
Answers
-
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
-
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,
-
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, " ", "*")
-
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
-
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?
-
Tagging some experts who might know more...
Please see if you can help when you get a chance.
-
give this a try.
=LEFT([Column Test]@row, FIND(" ", substitute([Column Test]@row,CHAR(10)," ")) - 1
-
It also gives me the first 2 words.
(FYI See my last 2 comments updated)
-
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,
-
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
-
can you copy and paste just the text, so I can potentially try to figure out what the format of the space is?
-
123 this and that
Here it is...!
-
Can you maybe VLOOKUP the space in your Smartsheet?
Thanks,
-
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..)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
