Is the number/letter combo always following the word "Comp" (as all your examples show)?But I am not trying to extract numbers from my text I am just trying to change the case of the character following the number. See example text below:
[TABLE="width: 393"]
<tbody>[TR]
[TD]"Comp 5c Train 2 Stage 1 Flocculator No.3 Simocode[/TD]
[/TR]
[TR]
[TD]Comp 6a Train 2 Stage 2 Flocculator No.1 Variable Speed Drive[/TD]
[/TR]
[TR]
[TD]Comp 6a Train 2 Stage 2 Flocculator No.1 Simocode[/TD]
[/TR]
[TR]
[TD]Comp 6b Train 2 Stage 2 Flocculator No.2 Variable Speed Drive[/TD]
[/TR]
[TR]
[TD]Comp 6b Train 2 Stage 2 Flocculator No.2 Simocode[/TD]
[/TR]
[TR]
[TD]Comp 6c Train 2 Stage 2 Flocculator No.3 Variable Speed Drive[/TD]
[/TR]
[TR]
[TD]Comp 6c Train 2 Stage 2 Flocculator No.3 Simocode"[/TD]
[/TR]
</tbody>[/TABLE]
=SUBSTITUTE(A1,MID(A1,5,3),UPPER(MID(A1,5,3)))
The reason I asked about the sentence structure was if the answer to either of the first three questions was yes, then the needed code could be more efficient than what follows. Assuming your data is in Column A starting on Row 1, the following macro will do the requested upper casing in place (within the data cells themselves)...Hi Rick,
The text in the sentence is not the issue. The text 1a of 6h is what I want I am interested in. The example text was highlighted in bold to point out the item's I was looking at. I am just after something so search for a Number Letter combination and change the case of the letter to Upper.
[table="width: 500"]
[tr]
[td]Sub UpperCaseNumberLetterCombos()
Dim R As Long, X As Long, Arr As Variant, Words() As String
Columns("A").Replace Chr(160), " ", xlPart, , , , False, False
Arr = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For R = 1 To UBound(Arr)
Words = Split(Arr(R, 1))
For X = 0 To UBound(Words)
If Words(X) Like "*#[a-z]*" And Not Words(X) Like "*[a-z]#*" Then Words(X) = UCase(Words(X))
Next
Arr(R, 1) = Join(Words)
Next
Range("A1").Resize(UBound(Arr)) = Arr
End Sub[/td]
[/tr]
[/table]
That is what the code in Message #14 does (provided the text is in Column A)....so i need to be able to search for a Number letter combination that could be anyware in the text i am searching