String right function not working properly

[karthikeyan1504]

Hi all,

I have a single field value as mentioned below,

KSN_PXRU4471    001

From above value, I need the characters after underscore 'PXRU4471    001'.

When I tried with the formula of '=Right(ITEM_KEY,Index(ITEM_KEY,'_'))', I get an output as '0001'.

Could you please let me know whether I am missing anything?

Thanks & Regards,

Karthikeyan.

[sunny_talwar · Accepted solution]

Have you tried SubField?

=SubField('KSN_PXRU4471    001', '_', 2)

or

=SubField('KSN_PXRU4471    001', '_', -1)

[sunny_talwar]

If you want to use Right, you can try this:

=Right('KSN_PXRU4471    001', Len('KSN_PXRU4471    001') - Index('KSN_PXRU4471    001', '_'))

[arulsettu]

may be this

Right('KSN_PXRU4471    001',15)

[#5]

Hi

It is better to use Subfield function for the data extraction when the data seperated by any delimiter.

[nico_ilog]

Try this,

Right(YourFieldName, len(YourFieldName)-Index(YourFieldName, '_') as YourField

Nico

[tamilarasu]

There are many ways to get the result. Subfield looks simple compared to other methods

=SubField(' KSN_PXRU4471    001', '_', 2)

=Right('KSN_PXRU4471    001', Len('KSN_PXRU4471    001') - FindOneOf('KSN_PXRU4471    001', '_'))

=Mid('KSN_PXRU4471    001', Index('KSN_PXRU4471    001', '_')+1)

[swuehl]

You can get what you want with an expression like above.

But why do you think the right() function is not working properly?

"right(text(ITEM_KEY),4)', I get an output as '0001'."

0001 are the four rightmost  characters in ITEM_KEY. So I can't see any issue with Right() function here.

Or how do you expect the Right() function to work?

[rubenmarin]

Hi Karthikeyan

Right() will return the last X characters of a string, so '=right(text(ITEM_KEY),4) can be translated as "give me the 4 last characters of each ITEM_KEY.

For your intentions it seems that Mid() is what you were looking for, something like: =Mid(Text(ITEM_KEY), 5), wich can be translated as "give me all the string from the 5th characther (removing the first 4 characters)

The options proposed by other users can also work.

[karthikeyan1504]

I am sorry. I have edited my question now.

The function I have used is =Right(ITEM_KEY,Index(ITEM_KEY,'_'))