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

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
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.…