How to extract a number from a string?

Question

abc = Sheets("02").Range("calc02").Value  // value is: Rabat 5%
xR = Mid(abc, 7, 1) // Result is: 5

This is ok, but what if the content is: Rabat 50%, or Rabat 100%
I want to extract this number only, whether it is one, two or three-digit.

Does your string always contain the numeric portion at the end? — brettdj
– brettdj, Commented Aug 21, 2012 at 6:26

Tony Basile · Accepted Answer · 2012-08-21 06:06:01Z

2

Use a regular expression:

var number = Regex.Match(abc, "[0-9]+");

answered Aug 21, 2012 at 6:06

Tony Basile

4193 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JMax Over a year ago

But it could work with some syntax changes: macrostash.com/2011/10/08/…

chris neilsen · Accepted Answer · 2012-08-21 06:08:45Z

1

xR = Mid$(abc, 7)  ' Returns 5% or 50% etc
xR = Left$(xr, Len(xR)) - 1)  ' strips the %

or, if you want xR returned as a number

xR = Val(Mid$(abc, 7))

answered Aug 21, 2012 at 6:08

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Comments

RocketDonkey · Accepted Answer · 2012-08-21 15:40:13Z

1

What about:

xR = Mid(abc, 7, Len(abc)-7)

Assuming the string is always the same, this takes the length of the string into consideration to dynamically return the value. This will only work with that format, but that sounds like what you want :)

If you instead wanted the percentage AND the word(s) in front were if variable length, this may work:

percent = Right(abc, InStr(StrReverse(abc)," ")-1)
xR = Mid(percent, 1, Len(percent)-1)

edited Aug 21, 2012 at 15:40

answered Aug 21, 2012 at 6:06

RocketDonkey

37.4k8 gold badges83 silver badges84 bronze badges

Collectives™ on Stack Overflow

How to extract a number from a string?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related