VBA how to select specific characters from a string

ivavok

New Member
Joined
Sep 1, 2009
Messages
11
Hello,

could somebody help me with this please? In one column I have different objects separated by a comma. I need to select one of these: 11,20,30,60,61 and copy it into another column. I have used this code:

For counter = 0 To not_empty_cells

For counter_dep = 1 To 5

position = InStr(1, (Cells(counter + 3, 4)), department(counter_dep))

If position > 0 Then
symbol_dep = Mid(Cells(counter + 3, 4), position, 2)

Cells(counter + 3, 10).Value = symbol_dep
End If

Next counter_dep

Next counter

It works, however, once in the first column there are the following objects: 60,6128,CZ, it takes 61 but it should take 60. Unfortunately, the position of the object can vary, it is not always on the first position.

Thank you
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I only want to extract one of these:11,20,30,60 or 61- only the one with two not more characters...
 
Upvote 0
Yes, but which one do you want to extract? How do you determine which one out of the string you want? Is it just one of 11,20,30,60,61? If so, if there are multiple instances (ie both a 11 and a 30) which one would you go for?
 
Upvote 0
Here's a function you can use:

Code:
Function get_element(ByVal s As String) As String
Static num_list
Dim i As Long

If IsEmpty(num_list) Then _
    num_list = VBA.Array(11, 20, 30, 60, 61)   'list of numbers to find
    
For i = 0 To UBound(num_list)
    If InStr(1, "," & s & ",", "," & num_list(i) & ",") > 0 Then _
        get_element = num_list(i): Exit Function    'if one of our numbers found, return found number and exit the loop
Next i

get_element = "Not found!"   'if we get here, none of the list of numbers has been located
End Function

Use in your code like this:

Code:
For Each cell In Range("A1:A10")  'amend as appropriate

  cell.Offset(,1).Value = get_element(cell.Value)   'write results into adjacent B column cells

Next cell
 
Upvote 0
I want to extract one of those and only one of those can appear (it can never happen that there are both 11 and 30. however, it can happen that there is also another kind of object such as 6128..and once it is there my code take 61 from it which is not correct.
 
Upvote 0
Unfortunately it does not work:(now I am wondering why..Here is an example:
column D: 60,CZ
column J: ,CZ
Column D is the one from which it is extracted
Column J: where it is copied
It should have copied 60...

Have you got any idea?thanks
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top