Extract Right VBA

[billandrew]

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

Thanks for any help...

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Before[/TD]
[TD]After[/TD]
[/TR]
[TR]
[TD]WEST 1 (LEFT)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]WEST 1 W[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]WEST 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]SOUTH 1 (RIGHT)[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Does it always follow the format:
- one word
- one space
- number
- another word (or end of string)?

So, we could say that the number ALWAYS appear after the first space in the string?

Are you just wanting to replace the existing entry with the number (in the same cell)?

 

[Joe4]

If my previous assumptions are correct, then a simple Text to Columns, returning only the second column when using the space as a delimiter, should work.
The VBA code for that would look something like:

Sub MyGetNums()
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 9), Array(2, 1), Array(3, 9)), TrailingMinusNumbers:=True
End Sub

 

[billandrew]

One exception may occur, there may not be a word following the number.

 

[Joe4]

One exception may occur, there may not be a word following the number.

Yes, that is what I meant by:

- another word (or end of string)?

See if the code in my previous post works for you (assuming data in column A).

 

[billandrew]

Works Great for the examples provided. Thank You

Although there may be text strings such as.

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]West 1 (North Region)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]West 2 (Pacific Region)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In which case there is an additional space.. ugh

 

[Joe4]

I think if you just add another field value, it should work, i.e.

Sub MyGetNums()
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 9), Array(2, 1), Array(3, 9)[COLOR=#ff0000], Array(4, 9)[/COLOR]), TrailingMinusNumbers:=True

End Sub
Note in adding additional field, the first number in the array is the field number (it is sequential), and the second is the data format (9 is "Do Not Import (skip)").

 

[billandrew]

awesome.

How does the array work? The 1, I see is for each field (The example has 4 fields). The second array (Number 1) captures the number?

Thank You very much

 

[Joe4]

The second array (Number 1) captures the number?

the second is the data format (9 is "Do Not Import (skip)").

If you perform a Text to Columns manually, on Step 3, you choose the format of each field the options are:
General - this is "1" (used for numbers)
Text - this is "2"
Date - this is "3"
Do not import column (skip) - this is 9

So the second field is the one we want, and we use "1" for that. The rest we use "9", since we do not want to import.

 

[billandrew]

Great stuff

thanks for teaching me to fish.