Capture Last Number in a Sequence?

Lisa Giddens-White

Hi-

I have these lesson ranges across columns on a row:

1-4; 5-11; 12-16; 17-22

What formula can I use to capture the last number in the sequence (22, in this case)?

Thanks in advance,

LGW

Lisa Giddens-White

Thank you, @Paul Newcome and @Leibel Shuchat. I appreciate your quick solutions.

Paul Newcome

@Leibel Shuchat Could you please take a look at the below to make sure I understood and adapted properly your solution from the other day where you pulled the n section out of a text string (link to that thread at the bottom of this post).

@Lisa Giddens-White Let's give this a try...

First we find out how many hyphens there are:

=LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, "-", ""))

Now that we know how many hyphens there are, we can use another SUBSTITUTE function to replace the last one with another special character that won't be found anywhere else within the string.

=SUBSTITITE([Column Name]@row, "-", "!", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, "-", "")))

Now that we have made the last one unique, we can use a FIND function to get the position number of that character.

=FIND("!", SUBSTITITE([Column Name]@row, "-", "!", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, "-", ""))))

Adding 1 to this will be the position number of the first character that we want to pull.

=FIND("!", SUBSTITITE([Column Name]@row, "-", "!", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, "-", "")))) + 1

Subtracting that from the total number of characters in the string will give us the total number of characters that we want to pull from the right.

=LEN([Column Name]@row) - (FIND("!", SUBSTITITE([Column Name]@row, "-", "!", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, "-", "")))) + 1)

Now that we know how many characters to pull starting from the right we can use that in the second portion of a RIGHT function like so:

=RIGHT([Column Name]@row, LEN([Column Name]@row) - (FIND("!", SUBSTITITE([Column Name]@row, "-", "!", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, "-", "")))) + 1))

.

@Leibel Shuchat Here's the link to the thread I am referencing:

https://community.smartsheet.com/discussion/comment/315285#Comment_315285

Leibel S

@Paul Newcome

Looks like a great use. Like the idea of using the LEN- LEN SUBSTITUTE to find the qty of items.

There is however an error in your formula. There is no need for the +1.

@Lisa Giddens-White

It seems like currently you have these across multiple columns?

So you would need to combine all that data into 1 cell (via a join formula) and then use use this formula created by Paul (replace the column name with whatever you call your column JOIN column is called)

=RIGHT([Column Name]@row, LEN([Column Name]@row) - (FIND("!", SUBSTITUTE([Column Name]@row, "-", "!", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, "-", ""))))))

Leibel S

One more thing, if you are looking for the highest number not the last one, see below link to another formula I put together with a similar application.

https://community.smartsheet.com/discussion/comment/308674#Comment_308674

For you to use it you would need to join your columns together and replace the empty spaces and dashes to be semicolons.

Paul Newcome

@Leibel Shuchat Thanks for the correction, and thanks for pointing out that it was across multiple columns. I had missed that.

@Lisa Giddens-White Since they are across multiple columns, we may be able to use a different formula if you didn't want to join them together. Are all of the columns next to each other? If so we could use an INDEX function looking across each of the columns on the row and a COUNTIFS function to count how many are filled in which tells us which column number to pull for the INDEX function.

=INDEX([First Column]@row:[Last Column]@row, 1, COUNTIFS([First Column]@row:[Last Column]@row, @cell <> ""))

From there we pull the rightmost characters using the same concept of LEN minus FIND.

=RIGHT(INDEX([First Column]@row:[Last Column]@row, 1, COUNTIFS([First Column]@row:[Last Column]@row, @cell <> "")), LEN(INDEX([First Column]@row:[Last Column]@row, 1, COUNTIFS([First Column]@row:[Last Column]@row, @cell <> ""))) - FIND("-", INDEX([First Column]@row:[Last Column]@row, 1, COUNTIFS([First Column]@row:[Last Column]@row, @cell <> ""))))

This is assuming that all of the columns are next to each other and there won't be any blank columns in between.

Honestly the most reliable (and I think the most efficient) solution would be to use a helper column where you join them all together and then use that first formula without the +1 (the corrected formula in Leibel's comment).

Lisa Giddens-White

Thank you, @Paul Newcome and @Leibel Shuchat. I appreciate your quick solutions.

Paul Newcome

@Lisa Giddens-White Happy to help. 👍️

Also thanks again @Leibel Shuchat for the input.