Extract the first word from a cell

L_123 · 08/15/22

still trying to figure this out. while i'm digging into it, can you use f12 mode to try to figure out the html callout for the type of space?

This particular one isn't on my unichar table that I can find, i've gone all the way through a couple hundred thousand unique unichars.

A Rose · 08/15/22

Unsure how to do that, you can try uploading the word document to a drive so you can check F12 or inspect,

Thanks,

L_123 · 08/15/22

ok... i'm at a loss. maybe the others can help you, but i haven't been able to find a good solution.

Until then, here is a rather stupid workaround.

in an empty cell post something like "A A" with the special character as the space. Then use text parsing to look at the second character of that cell to get your return for the whole column..

=LEFT([Column Test]@row, FIND(" ", SUBSTITUTE([Column Test]@row, MID($A$1, 2, 1), " ")) - 1)

Andrée Starå · 08/16/22

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å · 08/16/22

This is a great resource.

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

123U+0009thisU+0020andU+0020that

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

Paul Newcome · 08/16/22

@L_123 Haha. I built a similar sheet. "CHAR (1 - 255) and UNICHAR (1 - 200000)". I ended up breaking it down into multiple smaller sheets grouped together by type for easier browsing because the larger page definitely takes a while to load and scroll through.

@Andrée Starå I've bookmarked that first link. Definitely rather handy.

@A Rose Sorry I didn't join sooner, but hopefully Andree and L_123 were able to get you squared away.

L_123 · 08/16/22

Nice @Andrée Starå! I wonder why that didn't come up on my search for my unichar sheet.... i'll have to look into that. Thanks for the resource! bookmarked.

@Paul Newcome yeah, I was thinking about doing that while building the searches. Had to wait about a full minute for each lookup to calculate. Maybe that was the issue in why the formula didn't work.

Paul Newcome · 08/16/22

@L_123 It definitely made life a lot easier once I broke them down.

Not related to the post directly, but related to UNICHAR:

UNICHAR(11044) = Black circle

Conditional formatting colors (font 12 for size matching) gives you more options beyond RYGG or RYGB.

A Rose · 08/16/22

Wow!

Thanks so much @Andrée Starå , and @Paul Newcome and @L_123 you know your business and know it well!

Now let's complicate it just a bit more... 😉

I have this formula Thanks @Andrée Starå for always simplifying complications...!

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

What formula would I use for the entire column where some have a regular space, and some have this (UNICHAR(9)?

Thank you!

L_123 · 08/16/22

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

A Rose · 08/16/22

@L_123

Thanks you so much! that worked!

By the way, talking to experts like you, would you know why or how this CHAR(9) happens instead of a space?

It's a system we use, when exporting data some data get exported with that character instead of a space, is this an error on the software or in excel?

Thank you!

L_123 · 08/16/22

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