How to identify UTF-8 one-byte spaces (C2A0)

Hi!
I would like to use the Trim function to obtain up to 60 characters from the beginning.
However, if the 60th character is a one-byte space (C2A0) in UTF-8, it is not recognized by the Trim function, and as a result, a one-byte space is displayed in the 60th character.
Is there any way to avoid this?
If anyone knows, please help me.

Accepted answers

All comments

PulkitChawla

@KubotaNatsuki - did you try LEFT function? by putting formula something like - LEFT(Text,60). Can you try this and see if it gives you expected result?

KubotaNatsuki

Thanks for the reply!
A one-byte space is displayed in the 60th character.
If the 60th character is a one-byte space, I would like to delete it and make it 59 characters, but it is difficult to identify if the 60th character is a one-byte space.
Is there any solution to this problem?

andrewtye

Could you have a IF statement which is looking for the "space" as the last character and then left 59 otherwise do trim?

Amaya

@KubotaNatsuki

とても面白く、学びがありました。いい質問をありがとうございます。
今まで遭遇したことの無いケースでしたが、再現できました。確かにTRIMではスペースを消すことができないですね。

またIF文で処理しようとしたとき、Functionの中には「(C2A0) =  」を書くことができない（貼り付けても普通のスペースに読み替えられてしまう」というAnaplanの仕様があるのだと理解しました。

だとすると、何とか「 」だけのLINEITEMを持つことができれば、それを使って置換などができるのではないかと思います。
ということで、下記のようにしてみました。
まず を「X」でサンドイッチした文字列をdummy stringとして作ります。そのdummy stringの「X」を””にSUBSTITUTEで置換することで、純粋な をもつLINEITEMを作ることができました。

ということで、60ケタに落とした後（上記B/C）の、" "を""に再度SUBSTITUTEで置換できるようになり、59ケタに落とすことができるという方法を考えましたがいかがでしょうか？

多くの人は使うことがなさそうなテクニックですが、楽しめました。
参考になれば幸いです。

太一

Amaya

It was very interesting and I learned a lot. Thanks for the good questions.
I have never encountered this case before, but I was able to reproduce it. It is true that TRIM does not allow me to erase spaces.

I also understood that there is an Anaplan specification that "(C2A0) = " cannot be written in a Function when trying to process it with an IF statement (even if pasted, it will be read as a normal space).

If that is the case, I think that if we could somehow have a LINEITEM with only " ", we could use it to do replacements, etc.
So I tried the following.
First, create a dummy string by sandwiching with "X". By replacing the "X" in that dummy string with "" by SUBSTITUTE, we were able to create a LINEITEM with pure " ".

So, after dropping to 60 digits (B/C above), what do you think of the method of being able to replace "" with "" again by SUBSTITUTE and drop to 59 digits?

I know this is a technique that not many people seem to use, but I enjoyed it.
I hope this is helpful.

Taichi

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385