Convert text in alphanumeric string to lower case

[Dave T]

Hello All,

In a cell I have some text that is Proper case or Uppercase followed by an alphanumeric string then more text.

For example:
Item number - 139526R1V5 - Tim Smith

I would like all of the text within the alphanumeric part to be lowercase.
If it helps... the alphanumeric part will always start with a number/s and end with a number/s.
There are no patterns with the numbers after the R and the V in the alphanumeric string.

Is it possible to search for just the part that starts and ends with a number/s and have the macro only change this part the lowercase and the rest of the cells contents to Proper case ??

Thanks in advance,
Dave T

[JKwan]

is this what you wanted?

Sub test()
    Dim sString  As String
    Dim sSplitString As Variant
    Dim Index As Long
    
    sString = "Item number - 139526R1V5 - Tim Smith"
    sSplitString = Split(sString, " - ")
    sSplitString(1) = LCase(sSplitString(1))
    MsgBox sSplitString(1)
    
End Sub

[p45cal]

you can also (if originalstringis in A2):
=LEFT(A2,FIND("-",A2)) & LOWER(MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)) & MID(A2,FIND("-",A2,FIND("-",A2)+1),99)

[Dave T]

Thanks JKwan and p45cal for replying, I do appreciate it.

Your replies made me realise that I probably could have written the question differently.

Within a cell or range of cells there could be any text, not containing the specific text of 'Item number', there could be no dashes and there could be no end text after the alphanumeric part.
The only thing in common is that the alphanumeric string will start with a number and end with a number and this is primarily the part I would like to be converted to lower case.

When I was searching for how to do this I came across various posts that used .Pattern = "[A-Za-z0-9\-]{10,}"
I realise this example does not do what I am after, but I thought that as the string could start with any number, i.e. 1-9 or end with any number, i.e. 0-9 (and there being no spaces between the numbers) it might be a case of just find the part of the cell contents that matched this criteria and any text between the numbers was converted to lowercase.

I hope the above makes sense.

Once again, thanks for you replies.

Regards,
Dave T

[yujin]

What about this? In VBA regular expression, \d is equivalent to [0-9].

Sub Sample()
    Dim Match
    Dim testString As String
    
    testString = "Item number - 139526R1V5 - Tim Smith"
    
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d.+\d"
        .Global = True
        Set Match = .Execute(testString)
        If Match.Count > 0 Then MsgBox LCase(Match(0).Value)
    End With
    
    Set Match = Nothing
End Sub

[Dave T]

That is perfect yujin,

The message box returns the format I am after.
How would I go about physically replacing the uppercase values with the lowercase values in the selected cells.

Thanks for your reply.

Regards,
Dave T

[yujin]

OK, select the cells you want to replace the uppercase values with lowercase values and run the macro below.

Sub Sample()
    Dim rng As Range
    Dim str As String
    Dim Matches
    Dim Match
     
    For Each rng In Selection
        str = rng.Value
        With CreateObject("VBScript.RegExp")
            .Pattern = "\d.+\d"
            .Global = True
            Set Matches = .Execute(str)
            If Matches.Count > 0 Then
                For Each Match In Matches
                    str = Replace(str, Match.Value, LCase(Match.Value))
                Next Match
            End If
            rng.Value = str
        End With
    Next rng
    
    Set Matches = Nothing
End Sub

[Dave T]

Hello yujin,

Thank you very much for all you help.

Regards,
Dave T