Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
BI Consultant
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
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? 🙂
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.
BI Consultant
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
Num(subfield('111003333643-LOR-S1','-',1) ) this will give your desired result.
thanks
Sunil
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.
BI Consultant
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)
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!! 🙂
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! 🙂
hello
Please see the attached file .its working on by replacing '-' with '_'.
thanks
Sunil