Extract numbers - exact values?

S

Special-K99

Well-known Member

Joined

Nov 7, 2006

Messages

8,511

Office Version

1. 2019

• Mar 15, 2017

• #2

I dont think youll be able to do this smoothly, as you impossible as there's no pattern.
You could may be extract the rightmost number from the strings that would give you 11.000 5000 and 500 but it depends what youre other data is like.

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Mar 15, 2017

• #3

Special-K99 said:

You could may be extract the rightmost number from the strings that would give you 11.000 5000 and 500 ...

Click to expand...

Not by my reckoning. It appears to me that the right-most numbers would be
11.000
7
7

or
11.000
-7
-7

 

Upvote 0

A

apo

Well-known Member

Joined

Nov 3, 2008

Messages

581

• Mar 15, 2017

• #4

With such a small dataset.. it is impossible to extract any kind of legitimately recurring dataflow (that can be split/counted etc)

With 3 lines as an example.. who would know?

 

Last edited: Mar 15, 2017

Upvote 0

E

eduzs

Well-known Member

Joined

Jul 6, 2014

Messages

704

Office Version

1. 2019
2. 2010

Platform

1. Windows

• Mar 15, 2017

• #5

Try this formula before get the numbers from string:

=SUBSTITUTE(SUBSTITUTE(A6,LEFT(A6,12),""),"R-7","")

Note that with this formula, the number that you want must be always after the 12º caracter of the string.

 

Last edited: Mar 15, 2017

Upvote 0

S

Special-K99

Well-known Member

Joined

Nov 7, 2006

Messages

8,511

Office Version

1. 2019

• Mar 15, 2017

• #6

Peter_SSs said:

Not by my reckoning. It appears to me that the right-most numbers would be
11.000
7
7

or
11.000
-7
-7

Click to expand...

R-7 is not a number.
I thiought common sense would indicate what I was aiming at.

Based on your -7 assumption

11.000 is not the right most number, the right most number is in fact 0

11.000

 

Last edited: Mar 15, 2017

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Mar 15, 2017

• #7

Hi

You have to give a stronger specification. Like Peter said, the last number is not what you want.

Can you say, for ex. that it's the last number preceded and followed by a space?

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Mar 15, 2017

• #8

Special-K99 said:

R-7 is not a number.

Click to expand...

I didn't suggest that it was. I did suggest that 7 and -7 are numbers.

Special-K99 said:

Based on your -7 assumption

11.000 is not the right most number, the right most number is in fact 0

Click to expand...

That would be the right-most digit by my reckoning. In general, a number is made up of 1 or more digits (& possibly decimal points, negative signs, exponents etc)

Rather than quoting your post, perhaps I should have quoted the OP's post which also described the requirement as "extract value from right". I'm sorry if I offended you.

Most likely the requirement is as pgc has described and you have assumed. I was just pointing out the ambiguity.

 

Upvote 0

L

ljubo_gr

Board Regular

Joined

Dec 6, 2014

Messages

244

Office Version

1. 2016

Platform

1. Windows

• Mar 15, 2017

• #9

I just need to: hhmmm My number will always be between CMR:30000litres (30000) and R-7(5to12 designations), so, first i have to find Left that CMR number, then right if exist that R- number, in between is my number. If there isn't R- number then my number will always be smaller than big CMR number. So formula must be so complex. I'm using excel2013. Thank you so much for your timE! I forgot, space" " is between numbers, always.

 

Last edited: Mar 15, 2017

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Mar 15, 2017

• #10

Hi

This is a solution with these 2 assumptions:
- you want the last integer between spaces
- your integer may have thousand separators that are "."

Try this udf:

Code:

Function GetNumber(s As String) As Long

With CreateObject("VBScript.RegExp")
    .Pattern = ".* (\d+(\.\d{3})*) .*"
    GetNumber = Replace(.Replace(s, "$1"), ".", "")
End With
End Function

In B1: =GetNumber(A1)
Copy down

 

Last edited: Mar 15, 2017

Upvote 0