Text replacement

ceb

New Member
Joined
May 5, 2011
Messages
14
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
 
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]
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?
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
@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)

Code:
=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.
 
Last edited:
Upvote 0
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)...
Code:
[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]
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top