# Extract Last Number in Text String

#### pto160

##### Active Member
HTML:
``````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?

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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?

No, the number of digits can vary.

Hi pto160
Try
Code:
``=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))``

No, the number of digits can vary.
Try this...

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

Thank you both. It works great.

Last edited:
Thank you both. It works great.
You're welcome. We appreciate the feedback!

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

Thereby being quicker, I'd imagine !

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).

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 ?

Replies
6
Views
574
Replies
20
Views
836
Replies
6
Views
129
Replies
7
Views
325
Replies
6
Views
173

1,203,073
Messages
6,053,379
Members
444,660
Latest member
Mingalsbe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back