Hi,
I have lots of tools that are tagged such as the following
RC-B21-1-5
TB-R23-15-2
DR-R21-21-1
However, i want the scanner to only scan the code prior to the 2nd last ‘-’
So u would want
RC-B21
TB-R23
DR-R21
Can anyone help me this? Thank you!
Hi @Zikri_Zainal Maybe you could add another column and extract those 6 digits using LEFT eg LEFT([Barcode col],6)
Thank you Lynn
I forgot to mention some codes have more than 6 letters at the front. Ie
SB250-R19-2-1
SE3-R19-1-1
So they are not regular. How do i go about this?
Thank you
Ahh, You will need a more complicated formula then. Possibly using FIND to find the second -
LEFT(“SB250-R19-2-1” , FIND(“-” , “SB250-R19-2-1”)+3)
would give you
SB250-R19
This is usable if the “R19” part has always 3 characters.
Assuming all the code is constructed with 4 parts separated by 3 of ‘-’, like those.
column name = [QR_Code]
RC-B21-1-5
TB-R23-15-2
DR-R21-21-1
SB250-R19-2-1
SE3-R19-1-1
If this is the case, probably following expression could help, although i m not testing yet.
index(split([QR_code],’-’),1)&’-’&index(split([QR_code],’-’),2)
–
Using split expression will generate something like ARRAY out of the original test.
You always would like to first 2 parts of the text , so just take one by one using index expression.
Then contact two parts to get the texts in your required format.
This expression does not care how many characters each part of code is comprised of.
This is kind of playing with code… object and array…
@tsuji_koichi Very nice. Thank you!
I just tried it and it is working. Will surely need it in the future
index(split("RC-B21-1-5",'-'),1)&'-'&index(split("RC-B21-1-5",'-'),2)
Gives you RC-B21
By the way: I didn’t know that we can also use single quote ' instead of double quotes " in expressions
Hi Fabian,
Glad to hear you love this trick.
I think we have load of use cases with this .
Yes, single quote is also work.
Fabian:
I didn’t know that we can also use single quote
'instead of double quotes"in expressions
Indeed, you can get away with things like this:
concatenate(’“’, “My quote here”, ‘”’) which will give you: “My quote here”
If you encapsulate the double quote in single quotes (kinda hard to see this in the formula), the system ignores the double quote inside the single quote - single quotes are a way of saying ‘this is text, it’s always text.’
Try SUBSTITUTE((TOP(SPLIT([Column], "-"), 2) & ""), " , ", "-").
Thank you so much! Will definitely try this!
tsuji_koichi:
index(split([QR_code],’-’),1)&’-’&index(split([QR_code],’-’),2)
Thank you for shining the light of the rising sun on my dim heart. This worked perfectly!
You are welcome.