Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Left function for numbers

Highlighted
7 - Meteor

Hi all, 

 

silly question, but I have a column of numbers.  I would like to extract the first two numbers from the number string.  e.g 997, result 99.  The column type is numeric.  How do I do this?  Thanks!

 

JC

Highlighted
ACE Emeritus
ACE Emeritus

So the easiest way to do this in one formula is to use TOSTRING and TONUMBER to convert it to a string, get the left characters, and then get the number value back.

 

Here's an example (replace [yourfield] with your field)

 

TONUMBER(LEFT(TOSTRING([yourfield]),2))
Highlighted
8 - Asteroid

You can use this formula:

 

TrimLeft(ToString([Field1]),"2")

Highlighted
7 - Meteor

Another way to extract the first two numbers from the number string is to convert the column type from numerical to a string using select tool and then to add RegEx tool with an expression like this  ^(\d{2}) using parse method. I attached a simple one.    

Highlighted
7 - Meteor

Thanks Claje!

 

I was trying a combination of these and obviously did not have the right combination.  I really appreciate this solution!!

 

VR

JC

Highlighted
7 - Meteor

Thanks @Jim7 !

 

I really appreciate the solution.  I will try it out!

 

VR

JC

Highlighted
7 - Meteor

Thanks @Bahouth12

 

I had thought about running the conversion.  This is a great solution and will be trying it out.  Thanks for the example too.

 

VR

JC

Labels