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
Get the number at the end
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Get the number at the end
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Get the number at the end
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.
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.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Get the number at the end
Nice one Hans.
I was wondering how to deal with the decimal point.
Your sneaky approach handles that very smartly.
I was wondering how to deal with the decimal point.
Your sneaky approach handles that very smartly.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Get the number at the end
Thank you very much @Hans ;)
(I'll have to try and break it down..)
(I'll have to try and break it down..)
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Get the number at the end
Use the Evaluate Formula feature in Excel. It shows exactly how the formula calculates to the final value...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Get the number at the end
Thank you @Rudi. Yes, I started to look at Evaluate. (It doesn't always help, sometimes it just skips to the end ;) )
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Get the number at the end
Out of curiosity do you actually need 4.00 returned rather than 4?
Regards,
Rory
Rory
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Get the number at the end
It could be 4.25, etc.
4.00 was a poor single choice ;)
4.00 was a poor single choice ;)
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Get the number at the end
Then just as an alternative:
=-LOOKUP(2,-RIGHT(A1,ROW($1:$255)))
=-LOOKUP(2,-RIGHT(A1,ROW($1:$255)))
Last edited by rory on 12 Nov 2014, 14:19, edited 1 time in total.
Regards,
Rory
Rory
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Get the number at the end
No... short and sweet!HansV wrote:Nice and short!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.