Text replacement

[ceb]

I have a text in an Excel worksheet that contains "1a" and "3b" and i want to change the letters to uppercase with out changing the rest of the text case

Cheers Clive

 

[Rick Rothstein]

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:

"Comp 5c Train 2 Stage 1 Flocculator No.3 Simocode

Comp 6a Train 2 Stage 2 Flocculator No.1 Variable Speed Drive

Comp 6a Train 2 Stage 2 Flocculator No.1 Simocode

Comp 6b Train 2 Stage 2 Flocculator No.2 Variable Speed Drive

Comp 6b Train 2 Stage 2 Flocculator No.2 Simocode

Comp 6c Train 2 Stage 2 Flocculator No.3 Variable Speed Drive

Comp 6c Train 2 Stage 2 Flocculator No.3 Simocode"

<tbody>
</tbody>

Is the number/letter combo always following the word "Comp" (as all your examples show)?

If yes, is the word "Comp" always the first word in the sentence?

If not, is it always the second "word" in the sentence?

Are some or all of the letter/number combos bold as shown in your examples and, if so, does the bold text have to be retained?

 

[ceb]

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.

Clive

 

[Haluk]

@ceb;
If all your data has the same structure as in your last post No:10, you can try the following formula.

(Assuming, your data set starts from cell A1, you can copy down the formula)

=SUBSTITUTE(A1,MID(A1,5,3),UPPER(MID(A1,5,3)))

Note: I have seen messages No:11 and 12 after my post. I guess, Mr. Rothstein will suggest a more generic solution to you.

 

[Rick Rothstein]

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.

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

[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]

 

[ceb]

Thank Rick,
yes i know that works but In some of my text I will be looking at the positions will be different so i need to be able to search for a Number letter combination that could be anyware in the text i am searching, as it is taken from electrical drawings.

Clive

 

[ceb]

Rick,
Tried the macro you sent me and it works fine does exaxtly what i was after.
I will need to modify it to accept a selected range rather than a fixed A1

Thank you very much for your help

Clive

 

[Rick Rothstein]

...so i need to be able to search for a Number letter combination that could be anyware in the text i am searching

That is what the code in Message #14 does (provided the text is in Column A).