Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 17, 2015 7:10 AM
(13087 views)

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 5

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Thanks. Exactly what I want.

Bo

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
Feb 17, 2015 7:44 AM
| Last Modified: Oct 18, 2016 3:35 PM
(12870 views)
| Posted in reply to message from Bo 02-17-2015

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

It would look like this:

**(**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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Thank you for the fast response.

Bo

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
Feb 17, 2015 10:32 AM
| Last Modified: Nov 9, 2016 2:01 AM
(12870 views)
| Posted in reply to message from Bo 02-17-2015

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

Article Labels

There are no labels assigned to this post.