Reference part of number

[skeen503979]

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

[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.

[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 · Accepted solution]

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 : )

[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!