modify cell contets

windsurfer · #1 (**permalink**) 01-18-10, 06:23

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

Colin Legg · #2 (**permalink**) 01-18-10, 09:31

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.

Quote:

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.

Quote:

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).

Quote:

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...

windsurfer · #3 (**permalink**) 01-19-10, 02:14

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.

Colin Legg · #4 (**permalink**) 01-19-10, 05:45

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech