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?
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
-
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
-
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
-
Good answer Atus.. you had me by 4 minutes.....
-
Yes! Perfect, exactly what I was looking for! Thanks for the quick response!
-
One more question.
Are all of the Sheet ID's in Smartsheet 15 digits long?
-
I just looked at a few of my sheets and they all have a 16 sheet ID digit number.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives