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