Get the number at the end

agibsonsw · Post by **agibsonsw** » 11 Nov 2014, 15:46

Hello.

If I have a variety of text values in a column:

£12
Euro 4
£4
Euro 4.00

how can I extract just the numbers at the end please? 12, 4, 4, 4.00

Post by **HansV** » 11 Nov 2014, 16:00

Let's say the entries are in A1:A4.
Enter the following array formula in B1, confirmed with Ctrl+Shift+Enter:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),LEN(A1))

Fill down as far as needed.

Post by **Rudi** » 11 Nov 2014, 16:16

Nice one Hans.
I was wondering how to deal with the decimal point.
Your sneaky approach handles that very smartly.

agibsonsw · Post by **agibsonsw** » 11 Nov 2014, 17:48

Thank you very much @Hans ;)

(I'll have to try and break it down..)

Post by **Rudi** » 11 Nov 2014, 17:53

Use the Evaluate Formula feature in Excel. It shows exactly how the formula calculates to the final value...

agibsonsw · Post by **agibsonsw** » 11 Nov 2014, 18:01

Thank you @Rudi. Yes, I started to look at Evaluate. (It doesn't always help, sometimes it just skips to the end ;) )

rory · Post by **rory** » 12 Nov 2014, 08:54

Out of curiosity do you actually need 4.00 returned rather than 4?

agibsonsw · Post by **agibsonsw** » 12 Nov 2014, 10:35

It could be 4.25, etc.
4.00 was a poor single choice ;)

rory · Post by **rory** » 12 Nov 2014, 13:32

Then just as an alternative:
=-LOOKUP(2,-RIGHT(A1,ROW($1:$255)))

Post by **HansV** » 12 Nov 2014, 13:41

Nice and short!

Post by **Rudi** » 12 Nov 2014, 14:48

HansV wrote:Nice and short!

No... short and sweet!

Eileen's Lounge

Get the number at the end

Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end

Re: Get the number at the end