Leading Zeros in Data Shuttle

Sara Campbell

Hi - I'm trying to set up a way for end users to do bulk adds to our user list (vs one at a time via a form) and I have all the fields mapped in data shuttle and am getting results, except the leading zeros are being stripped away. See examples below. Since it is a data merge I can't see how I would add the ' before the number without it being a manual adjustment.

Excel

Smartsheet

Genevieve P.

Hi @Sara Campbell

Will there always need to be a 0 in the front of your values, or is it possible that some of them won't have the 0 in your source data?

We could add the 0 in after the import via a Column Formula, like so:

="0" + [Column]@row

Then you could hide the column that's mapped in the workflow and only show the formula in your sheet with the correct values.

However if some of them don't have 0's in the original file, are they appearing on the right side of the cell in Smartsheet? This would indicate that Smartsheet is reading them as numerical, so we could use a formula like so:

=IF(ISTEXT([Column]@row), "0" + [Column]@row, [Column]@row)

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Genevieve P.

Hi @Sara Campbell

Will there always need to be a 0 in the front of your values, or is it possible that some of them won't have the 0 in your source data?

We could add the 0 in after the import via a Column Formula, like so:

="0" + [Column]@row

Then you could hide the column that's mapped in the workflow and only show the formula in your sheet with the correct values.

However if some of them don't have 0's in the original file, are they appearing on the right side of the cell in Smartsheet? This would indicate that Smartsheet is reading them as numerical, so we could use a formula like so:

=IF(ISTEXT([Column]@row), "0" + [Column]@row, [Column]@row)

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Sara Campbell

Yes our Employee IDs always start with a 0 - so this may work! Thank you. I'm in a tight spot where I can't change the source data at all so I have to force it in.

Sara