CODEWITHSUNDEEP
excelvba
Ting Ping
Ting Ping

Reputation: 1145

How to remove all the date formating in excel VBA?

I have the following code. However, what I found in the cell is that the output (i.e. SEP08) is converted to Sep-08 in the cell. How may i just display SEP08 as it is in the excel spreadsheet without the date format?

Dim StartMonth As String
Select Case Month(EarliestDate)
    Case "1"
        StartMonth = "JAN" & Right(Year(EarliestDate), 2)
    Case "2"
        StartMonth = "FEB" & Right(Year(EarliestDate), 2)
    Case "3"
        StartMonth = "MAR" & Right(Year(EarliestDate), 2)
    Case "4"
        StartMonth = "APR" & Right(Year(EarliestDate), 2)
    Case "5"
        StartMonth = "MAY" & Right(Year(EarliestDate), 2)
    Case "6"
        StartMonth = "JUN" & Right(Year(EarliestDate), 2)
    Case "7"
        StartMonth = "JUL" & Right(Year(EarliestDate), 2)
    Case "8"
        StartMonth = "AUG" & Right(Year(EarliestDate), 2)
    Case "9"
        StartMonth = "SEP" & Right(Year(EarliestDate), 2)
    Case "10"
        StartMonth = "OCT" & Right(Year(EarliestDate), 2)
    Case "11"
        StartMonth = "NOV" & Right(Year(EarliestDate), 2)
    Case "12"
        StartMonth = "DEC" & Right(Year(EarliestDate), 2)
    End Select

ActiveCell.FormulaR1C1 = StartMonth

Upvotes: 0

Views: 359

Answers (2)

glh
glh

Reputation: 4972

You should set the cell to text using number format @:

ActiveCell.NumberFormat ="@"
ActiveCell.FormulaR1C1 = StartMonth

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Precede the string with a back-tick like so: `SEP08

Alternatively, to set a large number of cells use the Cell Formatting (available by selecting a range and typing CTRL-1) and select Text formatting on the Number tab.

Upvotes: 1

Related Questions