Page 1 of 2 12 LastLast
Results 1 to 10 of 15

VBA how to select specific characters from a string

This is a discussion on VBA how to select specific characters from a string within the Excel Questions forums, part of the Question Forums category; Hello, could somebody help me with this please? In one column I have different objects separated by a comma. I ...

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    11

    Default VBA how to select specific characters from a string

    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

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,671

    Default Re: VBA how to select specific characters from a string

    So how do you know which element of the string you want to extract?
    Richard Schollar

    Using xl2013

  3. #3
    New Member
    Join Date
    Sep 2009
    Posts
    11

    Default Re: VBA how to select specific characters from a string

    Quote Originally Posted by RichardSchollar View Post
    So how do you know which element of the string you want to extract?
    I only want to extract one of these:11,20,30,60 or 61- only the one with two not more characters...

  4. #4
    New Member
    Join Date
    Sep 2009
    Posts
    11

    Default Re: VBA how to select specific characters from a string

    I only want to extract one of these:11,20,30,60 or 61- only the one with two not more characters...

  5. #5
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,671

    Default Re: VBA how to select specific characters from a string

    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?
    Richard Schollar

    Using xl2013

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,671

    Default Re: VBA how to select specific characters from a string

    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
    Richard Schollar

    Using xl2013

  7. #7
    New Member
    Join Date
    Sep 2009
    Posts
    11

    Default Re: VBA how to select specific characters from a string

    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.

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,671

    Default Re: VBA how to select specific characters from a string

    Did you try the suggested code?
    Richard Schollar

    Using xl2013

  9. #9
    New Member
    Join Date
    Sep 2009
    Posts
    11

    Default Re: VBA how to select specific characters from a string

    Unfortunately it does not worknow 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

  10. #10
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,671

    Default Re: VBA how to select specific characters from a string

    Please post the full code you are using so that we can review and determine where the problem is. Thanks
    Richard Schollar

    Using xl2013

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com