• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert na, NA, Na, or nA to N/A in columns E and F

Eloise T

Active Member
The following macro successfully changes any characters in Columns E and F to upper case.

I added a 2nd line to convert/change/replace any occurrence of na, Na, nA, or NA in Columns E and F to N/A:

.Value = .Parent.Evaluate(Replace("IF(#=""na"",UPPER(#),""N/A"")", "#", .Address))
Currently the code wholesale changes everything found in Columns E and F to N/A....and that's not good. :eek:

Thank you in advance.

Code:
Sub ChangeCase()

'               Affects <Columns E and F>

    With Ws.[E3:F3].Resize(Ws.Cells(Rows.Count, "D").End(xlUp).Row)  '.Row - 2)

'        This changes all characters in the array defined by Columns E and F to UPPER case.
        .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))

'        I need the following line to change    na, Na, nA, or NA to N/A
'        In theory, any occurrence of na, Na, and nA are already changed to NA by virtue of the previous line containing the UPPER function, so the following line only needs to add a forward slash (/) to the NA.
        .Value = .Parent.Evaluate(Replace("IF(#=""na"",UPPER(#),""N/A"")", "#", .Address))

    End With
End Sub
 
Last edited:

Eloise T

Active Member
Both shrivallabha's and GCExcel's solutions worked and within a second of each when timed. Thank you both.
 
Top