Extract Last Number in Text String

[pto160]

assf 44 fdfd 4666
fdfdfgfgf 464 rttrtr 4646

How would you extract the last number in a text string. There is always a space before the last number?

 

[T. Valko]

assf 44 fdfd 4666
fdfdfgfgf 464 rttrtr 4646

How would you extract the last number in a text string. There is always a space before the last number?

Is the number always 4 digits long?

Does the number ever start with leading zeros?

 

[pto160]

No, the number of digits can vary.

 

[Michael M]

Hi pto160
Try

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[T. Valko]

No, the number of digits can vary.

Try this...

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[pto160]

Thank you both. It works great.

 

[T. Valko]

Thank you both. It works great.

You're welcome. We appreciate the feedback!

 

[Michael M]

Hmm, and your is a smidgen smaller, Biff !!

Thereby being quicker, I'd imagine !

 

[Rick Rothstein]

Hmm, and your is a smidgen smaller, Biff !!

Thereby being quicker, I'd imagine !

I think "smidgen" would apply to how much quicker Biff's code is than yours as well. Generally, but not absolutely, the number of function calls you make in a formula, not its length, determines its "quickness". Biff's formula substituted a hard code maximum length for the text being examined in place of your more flexible use of the LEN function; so, as long as the text in the cell is 100 characters or less, Biff's formula works and makes two less function calls than your formula, so it would be somewhat faster, however, the LEN function is a very fast executing function in Excel, so there is not a lot of gain in speed by replacing them with hard coded numbers. Biff's function does look more concise though (although I would use 99s instead of 100s to make it more concise).

 

[Michael M]

Hey Rick
Your obvious knowledge of function call speeds is impressive. This the 2nd time you've raised this on a thread I've been in.
How did you find this stuff out....timing a series of functions ??
OR, is there a magic place that has this information ?