Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Can I eliminate everything from a cell except for the end?

Jeff Bortner
edited 12/09/19 in Archived 2016 Posts

I would like to create a new column that will essentially copy over information from a different column but will only take the last few digits. For example, the first column will say "John's Business 290583958". The last digits would be the Sheet ID of "John's Business".

 

In the second column, I would copy over the information from the first column, but I would only take the last 10 digits (or however many are in a Sheet ID). That way I can use that cell to transfer data to different sheets.

 

Any help would be great! Thanks.

Comments

  • Atus Bartal
    Atus Bartal ✭✭✭✭✭✭

    Dear Jeff,

     

    If it was always a 10-digit ID, the formula in the second column would be quite simple: =RIGHT(Firstcolumn1,10) - it gives the right 10 digits of the 1st cell of the 'Firstcolumn'. if the length of the ID were different, it would be a bit complicated (a combined formula would be needed which I cannot figure out right now)...

     

    Atus 

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭
    edited 02/18/16

    Jeff,

     

    If I undersatnd what you are asking, you oculd use an formula called "Right" where it takes a specified # of characters from the Right side of the data.

     

    So the formula  "=RIGHT([Column2]2, 10)"    would take 10 characters from teh right side of the data in the cell in Column 2, Row 2.

     

    So if John's Business 290583958 in first cell, then only 290583958 is copied in the cell with the formula.  (Note the real # is only 9 digits.)

     

    Hope this helps.

     

    Tim

     

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    Good answer Atus.. you had me by 4 minutes.....Wink

  • Yes! Perfect, exactly what I was looking for! Thanks for the quick response!

  • Jeff Bortner
    edited 02/18/16

    One more question.

     

    Are all of the Sheet ID's in Smartsheet 15 digits long?

  • JohnHinkle
    JohnHinkle ✭✭✭✭✭✭

    I just looked at a few of my sheets and they all have a 16 sheet ID digit number. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 02/26/16

    My SheetID's are also 16 digits.

     

    Can I ask how you are populating the data?

     

    If I were typing this in, I would instead have three columns (versus your two -- I like columns)

     

    SheetID

    Name (or Description)

    Combined

     

    where

    SheetID = 290583958

    Name = John's Business 

    Combined = [Name]23 + " " + [SheetID]23

    for row 23.

     

    One advantage - not having to deal with pesky variable length SheetID's

    One disadvantage - creating pesky variable length SheetID for ones that have leading zeroes.

     

    There's a formula to fix that, of course.

    (Ooh, that might be my new tagline)

     

    Craig

This discussion has been closed.