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.

Find more posts tagged with

Sheets

Formulas

Comments

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

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

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

Jeff Bortner

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

Jeff Bortner

One more question.

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

JohnHinkle

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

J. Craig Williams

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