Reference part of number

Hello,

This is a bit of an odd request, but here goes. I have a reference file that shows the first PART of a serial number. My data set contains the full serial number. How can I link the two so that I'm able to pull in additional data from the reference file base on those first few numbers of the serial number? In the example below I am trying to create a column showing the color.

I hope that makes sense.

Thank you!

Example:

Serial number

111111188888 - (result of workflow should say "Red")

111111280461 - (result of workflow should say "Blue")

111111375486 - (result of workflow should say "Yellow")

Serial number beginning	Characteristic
1111111	Red
1111112	Blue
1111113	Yellow

Help

Accepted answers

atcodedog05

Hi @skeen503979

Just adding another method to above methods. Using formula startswith.

IF StartsWith([Serials], "1111111") THEN "Red" 
ELSEIF StartsWith([Serials], "1111112") THEN "Blue" 
ELSEIF StartsWith([Serials], "1111113") THEN "Yellow" 
ELSE Null() ENDIF

Workflow:

Hope this helps : )

serial.yxmd

All comments

clmc9601

Hi @skeen503979,

One way for this use case specifically is to use the Find Replace tool. It has a configuration that allows you to look at the beginning of the fields. I used Select tools in this picture because Find Replace only works on strings, not numbers.

Other use cases might require extracting the first X characters of the serial, like with the Left() function or regex.

I hope this helps!

clmc9601

Here's the workflow if that's easier.

match on beginning of strings.yxmd

apathetichell

switch(right(left(tostring([field1]),7),1),"red","1","red","2","blue","3","yellow")

in formula tool will do this for you. assuming the first 7 are what you want.

atcodedog05

Hi @skeen503979

Just adding another method to above methods. Using formula startswith.

IF StartsWith([Serials], "1111111") THEN "Red" 
ELSEIF StartsWith([Serials], "1111112") THEN "Blue" 
ELSEIF StartsWith([Serials], "1111113") THEN "Yellow" 
ELSE Null() ENDIF

Workflow:

Hope this helps : )

serial.yxmd

skeen503979

Thank you @atcodedog05 , @apathetichell , @clmc9601! Those were all very helpful. I really appreciate the community. 👍

atcodedog05

Happy to help : ) @skeen503979 I like your profile pic it looks like R2D2

Cheers and have a nice day!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703