VBA to convert the text to upper case based on condition

[Rabiyudeen]

Hi everyone, I need a macro to change the text before .(dot) to upper case. Below are the sample data to be considered,

In the below table, In column A, We have the name Starvin.VINAYKUMAR, which should be STARVIN.KUMAR.

dEEpak.rajkumar_345 should be DEEPAK.rajkumar_345

Rafeil_nadal.a_324 should be RAFEIL_NADAL.a_324.

I am just a beginner in Excel VBA, So request your help on this. Thanks !

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Starvin.VINAYKUMAR_123[/TD]
[TD]Chennai,India[/TD]
[/TR]
[TR]
[TD]dEEpak.rajkumar_345[/TD]
[TD]Bangalore,India[/TD]
[/TR]
[TR]
[TD]Rafeil_nadal.a_324[/TD]
[TD]Hyderabad,India[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mick Peters]

You could try something like this
=UPPER(LEFT(D2,(FIND(".",D2 )-1))) & MID(D2,FIND(".",D2),30)
I hope this helps
Mick.

 

[Rabiyudeen]

I tried the below code and its throwing error. Could anyone please correct me if i am wrong.

Sub Test()
Dim Rng As Range
Dim c As Range
On Error Resume Next
Set Rng = Cells.SpecialCells(xlCellTypeConstants, 2)
Columns(1).Select
For Each c In Rng
c.Value = Upper(Left(A2, (Find(".", A2) - 1))) & Mid(A2, Find(".", A2), 30)
Next c
End Sub

 

[tygrrboi]

some issues I see.

You have selected all cells in your worksheet that have values in them for Rng. This includes the cells located in the Location column too where I assume you do not want to make changes.

Try setting your range to something like

With ActiveSheet
 Set Rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)

end with

no need to select Columns(1)... just delete that bit

and then you can use the formula

C.Value = Ucase(Left(C.Value, Instr(C.Value, ".")-1)) & Mid(C.Value, Instr(C.Value, "."), 30)

 

[tygrrboi]

Or try something like this

[COLOR=#333333]Sub Test()[/COLOR]
[COLOR=#333333]Dim Rng As Range[/COLOR]
[COLOR=#333333]Dim c As Range
[/COLOR]Dim Output As String

[COLOR=#333333]On Error Resume Next

[/COLOR]With ActiveSheet
    Set Rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

[COLOR=#333333]For Each c In Rng[/COLOR]

    Output = C.Value
    Mid(Output, 1, InStr(Output, ".")) = UCase(Mid(Output, 1, InStr(Output, ".")))
    C.Value = Output
[COLOR=#333333]
Next c[/COLOR]
[COLOR=#333333]End Sub

[/COLOR]

 

[Rabiyudeen]

Or try something like this

[COLOR=#333333]Sub Test()[/COLOR]
[COLOR=#333333]Dim Rng As Range[/COLOR]
[COLOR=#333333]Dim c As Range
[/COLOR]Dim Output As String

[COLOR=#333333]On Error Resume Next

[/COLOR]With ActiveSheet
    Set Rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

[COLOR=#333333]For Each c In Rng[/COLOR]

    Output = C.Value
    Mid(Output, 1, InStr(Output, ".")) = UCase(Mid(Output, 1, InStr(Output, ".")))
    C.Value = Output
[COLOR=#333333]
Next c[/COLOR]
[COLOR=#333333]End Sub

[/COLOR]

Perfect!! Working fine. Thanks!

 

[tygrrboi]

Good to hear

 

[Rabiyudeen]

Hello Everyone,

In the above case, How to modify the code if I want to delete the characters before the dot operator and "."? Thanks in advance!

INPUT:

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Starvin.VINAYKUMAR_123[/TD]
[TD]Chennai,India[/TD]
[/TR]
[TR]
[TD]dEEpak.rajkumar_345[/TD]
[TD]Bangalore,India[/TD]
[/TR]
[TR]
[TD]Rafeil_nadal.a_324[/TD]
[TD]Hyderabad,India[/TD]
[/TR]
</tbody>[/TABLE]










Output
[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]VINAYKUMAR_123[/TD]
[TD]Chennai,India[/TD]
[/TR]
[TR]
[TD]rajkumar_345[/TD]
[TD]Bangalore,India[/TD]
[/TR]
[TR]
[TD]a_324[/TD]
[TD]Hyderabad,India
[/TD]
[/TR]
</tbody>[/TABLE]