#### rjc4

##### Well-known Member
Hi All,

In U26:X200 is my data like below.
W has a number and color. The number can be one or
two digits. (eg. Vo4, GREEN or Vo53, PINK).

SA 2 Vo4, GREEN Mon
SA 4 Vo3, Pale GREEN Wed
VB 7 Vo1, BLACK Tues
HB 8 Vo4, Dark BLUE Mon

I have a combo box which gets the first two columns
Of data from my range and puts the result in G11 and H11.
So G11 is SA and H11 is 2.

This is the combo code that does this.
Code:
Option Explicit
Private Sub ComboBox4_Change()
With Me.ComboBox4
If .ListIndex > -1 Then
Me.Cells(11, "G") = .Value
'Me.Cells(11, "H") = Me.Cells(.ListIndex + 26, "v")
Me.Cells(11, "H") = Val(.List(.ListIndex, 1))
End If
End With
End Sub

However, I wish to also get the NUMBER ONLY in columnW
And insert this in cell J7. So say in the third record as example
G11=VB, H11=7 and J7=1.

Can anyone help me to do this.

RC

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### rjc4

##### Well-known Member
Hi again,
No replies so must assume not able to be done or too complex.

If anyone could tell me how then I can extract just the number, I think I can take it from there.

W has a number and color. The number can be one or
two digits. (eg. Vo4, GREEN or Vo53, PINK or Vo7, Light BLUE).
Just need to get the 4 or the 53 or the 7 from the cell.

Thankyou,
RC

Hi All,

In U26:X200 is my data like below.
W has a number and color. The number can be one or
two digits. (eg. Vo4, GREEN or Vo53, PINK).

SA 2 Vo4, GREEN Mon
SA 4 Vo3, Pale GREEN Wed
VB 7 Vo1, BLACK Tues
HB 8 Vo4, Dark BLUE Mon

I have a combo box which gets the first two columns
Of data from my range and puts the result in G11 and H11.
So G11 is SA and H11 is 2.

This is the combo code that does this.
Code:
Option Explicit
Private Sub ComboBox4_Change()
With Me.ComboBox4
If .ListIndex > -1 Then
Me.Cells(11, "G") = .Value
'Me.Cells(11, "H") = Me.Cells(.ListIndex + 26, "v")
Me.Cells(11, "H") = Val(.List(.ListIndex, 1))
End If
End With
End Sub

However, I wish to also get the NUMBER ONLY in columnW
And insert this in cell J7. So say in the third record as example
G11=VB, H11=7 and J7=1.

Can anyone help me to do this.

RC

#### acw

##### MrExcel MVP
Hi

Are they always prefixed with Vo??? Or can there be other prefixes, of various lengths?

Tony

#### rjc4

##### Well-known Member
G'day Tony,

Yes. Vo is short for Volume number.
The number is always either 1 or 2 digits preceeded by Vo only.
The colour description though can be one or several words.

Cheers,
RC

Hi

Are they always prefixed with Vo??? Or can there be other prefixes, of various lengths?

Tony

#### acw

##### MrExcel MVP

RC

Not sure where you have the data, but you can use the substitute function to remove the Vo and just leave the number

holder = val(worksheetfunction.substitute(range("W11").value, "Vo",""))
msgbox holder

Tony

#### rjc4

##### Well-known Member
Hi Tony,
My data is in W26 down. Can't seem to get that to work.

This is what I have and it works fine except it only extracts one digit.
Cell W26 contains [Vo10, Dark BLUE]
=TRIM(MID(W26,FIND(",",W26)-1,1))

Above formula gets the 0 not 10.
If I have Vo4, then it gets the 4 no problems.

I can't see what is needed to get 2 digits if the number has two.

Can you see where it is wrong.

Thanks again,
RC

RC

Not sure where you have the data, but you can use the substitute function to remove the Vo and just leave the number

holder = val(worksheetfunction.substitute(range("W11").value, "Vo",""))
msgbox holder

Tony

#### acw

##### MrExcel MVP

Hi

Try

Code:
``=VALUE(SUBSTITUTE(LEFT(W26,SEARCH(",",W26)-1),"Vo",""))``

Tony

#### rjc4

##### Well-known Member
Hello Tony,
Perfect!

Thank you very much.
RC

Hi

Try

Code:
``=VALUE(SUBSTITUTE(LEFT(W26,SEARCH(",",W26)-1),"Vo",""))``

Tony

#### rjc4

##### Well-known Member
Hi Tony,
You helped me with a formula the other day which works perfectly.
However, I've run into an unforseen problem.

Some of my cells contain text only (say Dark BLUE) and as a result, the formula returns #value.

Can you suggest a modification to the formula that returns a blank when the Vo number is not there.

Sorry to be a pain. I've had numerous goes at it and can't find a solution.

Regards,
RC

Hi

Try

Code:
``=VALUE(SUBSTITUTE(LEFT(W26,SEARCH(",",W26)-1),"Vo",""))``

Tony

#### rjc4

##### Well-known Member
Hi again Tony,
False alarm. I've worked it out using ISNUMBER(SEARCH parameters.

Cheers,
RC

Hi Tony,
You helped me with a formula the other day which works perfectly.
However, I've run into an unforseen problem.

Some of my cells contain text only (say Dark BLUE) and as a result, the formula returns #value.

Can you suggest a modification to the formula that returns a blank when the Vo number is not there.

Sorry to be a pain. I've had numerous goes at it and can't find a solution.

Regards,
RC

Hi

Try

Code:
``=VALUE(SUBSTITUTE(LEFT(W26,SEARCH(",",W26)-1),"Vo",""))``

Tony

Replies
1
Views
231
Replies
2
Views
257
Replies
21
Views
1K
Replies
14
Views
5K
Replies
14
Views
3K