3

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;@"
BigBen
  • 46,229
  • 7
  • 24
  • 40
Starky
  • 135
  • 6
  • 1
    It may be useful to create a table of the month Names and numbers and `vlookup` from the month #. `Concat` month with the last 2 numbers of the `year` and you should be about there without using VBA. `=vlookup(Month([date]),Table,2,0)&"-"&right(Year([date]),2)` – Mark S. May 18 '21 at 18:47

1 Answers1

3

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.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250