How to add single quote in front of all the rows in Excel using VBA

user_15562816 0
Viewed 345

I have a spreadsheet that contains first column with date, I want the text to stay as "May-21" but in the form of string. The cell value should be May-21 but when I try to convert it into string it using 5 digit number. If I keep the column in date format it'll automatically pick May first so even if the format is MMM-yy the value will be 5/1/2021, I want to treat the column as string not date any suggestions?

Here's what I have tried in VBA:

Columns("A:A").Select        
Selection.NumberFormat = "'[$-en-US]mmm-yy;@"
vba excel
1 Answers

Is this what you are trying?

This below method uses the INDEX() AND TEXT() formula to convert the entire range in one go. without looping. For more explanation you may want to see Convert an entire range to uppercase without looping through all the cells

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim sAddr As String

    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row in Col A in input sheet
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        Set rng = .Range("A1:A" & lRow)
        sAddr = rng.Address
        
        rng = Evaluate("INDEX(TEXT(" & sAddr & ",""'[$-en-US]mmm-yy;@""),)")
    End With
End Sub

In Action

enter image description here

Note: If you do not want to use VBA then use the formula =TEXT(A1,"'[$-en-US]mmm-yy;@"). Put this in Cell B1 and drag it down. Finally copy paste as values.

user_11405790
Related