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

 

[oldbrewer]

find 1a and replace with 1A - or is it more complicated ?

 

[ceb]

find 1a and replace with 1A - or is it more complicated ?

Yes I have a lot of text that contain a lot of this type 1a 3c 2f

etc, (they can be any number), and of I convert the text to proper case they are still lowercase, so I need something to find these and change the case. I have tried converting all to upper case and back to proper case but that does not work. Any ideas would be great.
​

Clive

 

[Daminc2]

Have you tried:

=UPPER(cell)

VBA version:

    ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"

 

[Daminc2]

Have a look at this bit of code too (I was helped out with this...)

Range("D" & RowValue).Select
        ActiveCell.FormulaR1C1 = "=MID(R[0]C[-1],SEARCH(""Band"",R[0]C[-1])+5,1)"

 

[ceb]

Upper just turns everything to uppercase which I don't want I just want to search for a number followed by a character an make the character uppercase.
i.e 1a, 5g, 9f = 1A, 5G and 9F. The rest of the text is in the correct case.

I could not get the last post code to work just stopped with an error on .Select

Clive

 

[Daminc2]

Sorry. I posted a clip from a macro that I use in order to highlight the use of MID and SEARCH in context that might of helped you isolate the items you wanted. I don't know if the bits you want occur at the same place within a cell or if you have to search for incidences etc.

 

[Daminc2]

This was the full clip of the macro:

    For Each rrow In rowRange
    RowValue = rrow.Row
    
        Range("D" & RowValue).Select
        ActiveCell.FormulaR1C1 = "=MID(R[0]C[-1],SEARCH(""Band"",R[0]C[-1])+5,1)"
    Next rrow

It looped through cells looking for the word "Band" and selected the number that was just after that word.
e.g Band 6, showed the number 6 etc.

 

[Daminc2]

I also found this solution which is a bit beyond my expertise:

{=MID([COLOR=blue]$A1,MIN([COLOR=red]SEARCH([COLOR=green]{0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"[/COLOR])[/COLOR]),MAX([COLOR=red]IF([COLOR=green]ISNUMBER([COLOR=purple]VALUE([COLOR=teal]MID([COLOR=#ff00ff]$A1,ROW([COLOR=navy]INDIRECT([COLOR=blue]"1:" & LEN([COLOR=red]$A1[/COLOR])[/COLOR])[/COLOR]),1[/COLOR])[/COLOR])[/COLOR]),ROW([COLOR=purple]INDIRECT([COLOR=teal]"1:" & LEN([COLOR=#ff00ff]$A1[/COLOR])[/COLOR])[/COLOR])[/COLOR])[/COLOR])-MIN([COLOR=red]SEARCH([COLOR=green]{0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"[/COLOR])[/COLOR])+1[/COLOR])}

Source: https://www.mrexcel.com/forum/excel-questions/959025-extract-number-text-string-cell.html

 

[ceb]

Thank you for your help Daminc2.
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]

As you can see the letters after the numbers are lower case I want something to search for them and set them to uppercase.

Clive