Extract Right VBA

[billandrew]

Trying to extract number value in string using VBA. The range dynamic.

Thanks for any help...

Before	After
WEST 1 (LEFT)	1
WEST 1 W	1
WEST 2	2
SOUTH 1 (RIGHT)	1

<tbody>
</tbody>

 

[Joe4]

You are welcome!
I like it when people don't just want answers, but want to understand the logic behind it!

 

[billandrew]

The answer in my opinion is the easy way out. Understanding the logic is the key to developing code on your own and then possibly assisting others as you and the many others on this forum continue to do daily.

Thanks again.

 

[Rick Rothstein]

As always, there is more than one way to code a routine, so here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub GetNum()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF({1},MID(LEFT(@,FIND("" "",@&"" "",FIND("" "",@)+1)),FIND("" "",@)+1,99))", "@", Addr))
End Sub[/td]
[/tr]
[/table]

And, while this looks "longer", it is actually quite fast...

[table="width: 500"]
[tr]
	[td]Sub GetNumWhichIsSecondField()
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Split(Data(R, 1))(1)
  Next
  Range("A1").Resize(UBound(Data)) = Data
End Sub[/td]
[/tr]
[/table]

 

[billandrew]

Mr. Rothstein

Thank You very much

 

[Joe4]

And, while this looks "longer", it is actually quite fast...

Are we in "bizarro" world, Rick?
Usually, it is me posting the "loop" solutions, and you posting the more concise non-loop solutions!

 

[Rick Rothstein]

Are we in "bizarro" world, Rick?
Usually, it is me posting the "loop" solutions, and you posting the more concise non-loop solutions!

Well, to be fair, I did post two routines the first of which was a non-looping solution.:wink: I included the looping solution as an afterthought, sort of to go along with my statement "As always, there is more than one way to code a routine". See, not so bizarre after all.

 

[Joe4]

See, not so bizarre after all

I guess maybe "ironic" would be a better word!
Maybe it is just amusing to no one else but me!

 

[Rick Rothstein]

I guess maybe "ironic" would be a better word!
Maybe it is just amusing to no one else but me!

No, I understand you bemusement completely. I will be the first to admit that some (a lot?) of my solutions would be hard to read/fathom by someone early in their programming career, so having a familiar looking looping solution in a thread is definitely useful. Usually, someone posts a looping solution in a thread like this, but no one did... and, as I said, my constructing/posting one was sort of an afterthought.

 

[Joe4]

I will be the first to admit that some (a lot?) of my solutions would be hard to read/fathom by someone early in their programming career, so having a familiar looking looping solution in a thread is definitely useful. Usually, someone posts a looping solution in a thread like this, but no one did... and, as I said, my constructing/posting one was sort of an afterthought.

Agreed. It is nice to show the different ways.

Its kind of funny actually. I started off doing a looping solution, they thought "Wait, we could just use Text-to-Columns here!". So I scratched my looping solution.