BookmarkSubscribeRSS Feed
Bo

Community Trekker

Joined:

Jul 31, 2014

How to represent numeric value "1" as "001"?

I have thousands of X and Y coordinates in two different columns and would like to combine the coordinates as 6-digit values into a single column. For example, combine (1,1) into 001001, and combine (14, 14) into 014014. A simplified data table is attached.

How do I do that using formula or script?

Bo
1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

Re: How to represent numeric value "1" as "001"?

Assuming X and Y never exceed three digits, this should work in a column formula:

Repeat( "0", 3 - Length( Char( :X ) ) ) || Char( :X ) ||

Repeat( "0", 3 - Length( Char( :Y ) ) ) || Char( :Y )

Edit:

Or maybe this is more efficient:

Right( Char( 1e6 + :X * 1e3 + :Y ), 6 )

5 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

Re: How to represent numeric value "1" as "001"?

Assuming X and Y never exceed three digits, this should work in a column formula:

Repeat( "0", 3 - Length( Char( :X ) ) ) || Char( :X ) ||

Repeat( "0", 3 - Length( Char( :Y ) ) ) || Char( :Y )

Edit:

Or maybe this is more efficient:

Right( Char( 1e6 + :X * 1e3 + :Y ), 6 )

Bo

Community Trekker

Joined:

Jul 31, 2014

Re: How to represent numeric value "1" as "001"?

Thanks. Exactly what I want.

Bo
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Re: How to represent numeric value "1" as "001"?

You can use the Repeat() function to pad out the character representation for these coordinates.

It would look like this:

8053_JMPScreenSnapz001.png

(Repeat( "0", 3 - Length( Char( :X ) ) ) || Char( :X )) || (

Repeat( "0", 3 - Length( Char( :Y ) ) ) || Char( :Y ))

I've attached your data table with a new column showing this.

-Jeff

-Jeff
Bo

Community Trekker

Joined:

Jul 31, 2014

Re: How to represent numeric value "1" as "001"?

Thank you for the fast response.

Bo
Craige_Hales

Staff

Joined:

Mar 21, 2013

Re: How to represent numeric value "1" as "001"?

the left and right functions have a third argument to specify a pad value.  JSL Character String Functions

x=42;

right( char(x), 3, "0");

"042"

be careful, some of the other suggestions may be more robust if the numbers are out-of-bounds, or maybe not, depending what you expect:

x=9876;

right( char(x), 3, "0");

"876"

Craige