Solved: I'm new and need some help with some cell editing in vba (Excel)

[poloalpha]

Hi guys,

I'm new to the world of VBA, and although im getting through OKish, im stuck on some things. The first thing is deleting a word in a cell that contains a sub-word, so for example, if we have cells like:
1000.2, old_version12_0
1800.4, new_version19_30
10050.2, new_version102_0
100.4, old_version2_0

I want something which will delete any word containing "version", so i get:
1000.2,
1800.4,
10050.2,
100.4,

My other problem is with splitting the contents of a cell and putting the two parts into variables, so if I have cells such as:
1000.2, a
1800.4, a, bc
10050.2, a, bc, a
100.4, c, aa, ab, a

I want whatever is on the RIGHT of the last comma into one variable, and whatever is on the LEFT of the last comma into another variable. i.e.
left var = 1000.2, right var = a
left var =1800.4, a, right var =bc
left var =10050.2, a, bc, right var =a
left var =100.4, c, aa, ab, right var =a

obviously though I'm aware we can't fit all these into the same variable, I just need it to work for one of the cells so I can process the information and then I will be looping through and doing the same for the next cell.

All help would be much appreciated!

[Bob Phillips]

[vba]

Dim pos As Long

With ActiveCell

pos = InStr(.Value2, "version")
If pos > 0 Then

pos = InStrRev(.Value2, " ", pos)
MsgBox Left$(.Value2, pos - 1)
End If
End With
[/vba]

[vba]

Dim var As Variant
Dim var1 As String, var2 As String

var = Split(ActiveCell.Value2, ",")
var1 = var(LBound(var))
var2 = var(LBound(var) + 1)
[/vba]

[GTO]

Greetings poloalpha,

I see you just joined, welcome to vbaexpress . I'm sure you'll be glad you did, as there's great follks here

As to your questions, just a different try:

As to the first bit (getting the left part of: 10050.2, new_version102_0), I'm not sure if we can count on the left part being a 'number', so this may well not be stellar. Just to see if it works, as a UDF:

Option Explicit
    
Function RETLEFT(ByVal CellString As String) As Variant
Static REX As Object
    
    If REX Is Nothing Then
        Set REX = CreateObject("VBScript.RegExp")
    End If
    
    With REX
        .Global = False
        .IgnoreCase = True
        .Pattern = "([0-9]+[.]?[0-9]*\,)(.*version.*)"
        If .Test(CellString) Then
            RETLEFT = .Execute(CellString)(0).SubMatches(0)
        Else
            RETLEFT = vbNullString
        End If
    End With
End Function

I was thinking that to split based on the last comma, maybe:

Function RETSPLIT(ByVal CellString As String) As Variant
Dim ary(1 To 2)
Dim Pos As Long
    
    Pos = InStrRev(CellString, ",")
    If Pos > 0 Then
        ary(1) = Left(CellString, Pos)
        ary(2) = Trim(Mid(CellString, Pos + 1))
    Else
        ary(1) = vbNullString
        ary(2) = vbNullString
    End If
    RETSPLIT = ary
End Function

...again as a UDF, but entered as an array formula across two cells.

Hope that helps, and again, welcome to VBAX :-)

Mark