Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    11

    Red face Unanswered: modify cell contets

    hello all,

    I wonder if I could use a macro in order to modify data in a column.

    specifically, I want it to track number in cells of a specific column and drop whatever is next to it!

    that is:

    data123-124 should become data123
    data123 & 5020 should become data123
    data 123 - data 125 should become data 123
    data 123b should become data 123
    d.a\t;a, 45A - 46B should become d.a\t;a, 45

    and so on and so forth...

    thank you all in advance

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    To use VBA to modify cell contents, there needs to be some sort of logical pattern you can include.

    In your examples, there isn't an obvious pattern (not obvious to me, at least).

    For example:

    In this case, the cell contains the text "data123-124" and it seems that you want to remove anything from the hyphen onwards.
    data123-124 should become data123
    In this example it seems that the cell contains the text "data123 & 5020" and you want to remove anything from the first space onwards.
    data123 & 5020 should become data123
    In this example, the cell seems to contain the text "data 123b" and you want to remove just the final character (ie. not from the first space).
    data 123b should become data 123
    Please can you clarify a set of rules as to what should be removed? Is it that you want to remove any characters which follow the first complete number?
    Please also clarify if this is a one-off event, or something that has to happen with regularity?

    Hope that helps...

  3. #3
    Join Date
    Nov 2006
    Posts
    11
    you have an alpharethmetic data string.
    I want a function to drop whatever is after the end of the first number that it measures; it could be a symbol, a letter or just a space.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Right.

    If you are familiar with regular expressions then you could use them to do this.

    Another option is to write your own function using the built-in VBA functions to do it. Here's an example:
    Code:
    Sub test()
        Debug.Print foo("data123-124")              'should become data123
        Debug.Print foo("data 123b")                'data 123
        Debug.Print foo("d.a\t;a, 45A - 46B")       'd.a\t;a, 45
        Debug.Print foo("data123 & 5020 ")          'should become data123
        Debug.Print foo("")
        Debug.Print foo("d")                        'should become d
    End Sub
    
    
    Function foo(ByRef strToCheck As String) As String
        
        Dim i As Long, lAsc As Long, lLength As Long
        Dim bNumberFound As Boolean
        
        lLength = VBA.Len(strToCheck)
        
        For i = 1 To lLength
            lAsc = VBA.AscW(VBA.Mid$(strToCheck, i, 1))
            
            If lAsc >= 48 And lAsc <= 57 Then
                bNumberFound = True
                    
            Else
                If bNumberFound Then
                    i = i - 1
                    Exit For
                End If
            End If
        Next i
        
        foo = VBA.Left$(strToCheck, i)
    End Function
    Hope that helps...
    Last edited by Colin Legg; 01-19-10 at 10:31.

Posting Permissions

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