Return value from ordernumber: 11100643-LOR

[anitamelbye]

Hi all!

Can anyone tell me how to return only 11100643 from this ordernumber (11100643-LOR)?

What if the ordernumber is 11100643-LOR-S1, and I still just want it to return 11100643 as my ordernumber?

What do I write?
(I don't want it to be done in the load).

Thanks in advance for any clever suggestion.

Regards, Anita

[kaushiknsolanki]

Hi,

    You can use the left function.

    like

    left('11100643-LOR-S1',8)

   This will give you only first 9 char from left side.

Regards,

Kaushik Solanki

[anitamelbye]

Thank you, but no, sorry, it will not work.

The number "11100643" is sometimes 9 digits and sometimes 8 and sometimes even up to 15..

Any other suggestions? 🙂

[Miguel_Angel_Baeyens · Accepted solution]

Hello Anita,

Try with the following:

=KeepChar(Left('111003333643-LOR-S1', Index('111003333643-LOR-S1', '-')), '0123456789')

That should cover all lengths and possibilities, provided the format is always a string of numbers followed by the dash "-" sign and you only want to keep the first part.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[anitamelbye]

THANK YOU! How wonderful! THANK YOU! That worked just great.

I see now that even some of the ordernumbers even are devided by _ ,

is it possible to write something like

=KeepChar(Left('111003333643-LOR-S1', Index('111003333643-LOR-S1', '-,_')), '0123456789')

Regards, Anita

[SunilChauhan]

Num(subfield('111003333643-LOR-S1','-',1) ) this will give your desired result.

thanks

Sunil

[Miguel_Angel_Baeyens]

Hello Anita,

This will do the trick (there are some other ways to do that using conditionals or changing the order of the functions, but this one should work)

=KeepChar(Left('111003333643_LOR-S1', Index(Replace('111003333643_LOR-S1', '_', '-'), '-')), '0123456789')

The first string may have "_" or "-" sign, but the index() function will always get the right position of the character. If there are multiple characters as separators, you can nest them

Replace(Replace(Replace('111003333643_LOR-S1', '_', '-'), '/', '-'), '+', '-')

So any of the characters in the first parameter are replaced by the second string.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: For what it's worth, Sunil's solution will work as well and is cleaner than mine, for example using the following:

SubField(Replace(Replace(Replace('111003333643_LOR-S1', '_', '-'), '/', '-'), '+', '-'), '-', 1)

[anitamelbye]

TO SUNIL:

It works when I write '111003336_LOR-S1', but when I replace the number with "PDONRT" (what is the ordernumber), you suggestions fails.. But thanks for responding to my question!! 🙂

[anitamelbye]

Miguel:

THANKS once again! It works. I am trying Sunils suggestion, but I cant get it to work. And I tried what you said. But as long as your suggestions works, I am satisfied! 🙂

[SunilChauhan]

hello

Please see the attached file .its working on by replacing '-' with '_'.

thanks

Sunil