CODEWITHSUNDEEP
excelvba
MB41
MB41

Reputation: 584

How would I extract the formatted text from a cell in VBA?

Lets say A1 is a date formatted to "YYMMDD" and I enter 7/7/2014 in the cell. The cells string changes to "140707" but the function bar still shows up as "7/7/2014"

When I try to write this code:

Dim dateCell As String
dateCell = Cells(1, "A")

dateCell will equal to "7/7/2014" and not "140707"

I want the cell to be formatted that way so that when anyone puts in a date, it'll automatically change it to yymmdd. Now how would I get the text dateCell to equal 140707 and not 7/7/2014?

I would greatly appreciate it!! Thanks

Upvotes: 1

Views: 2180

Answers (2)

Joe
Joe

Reputation: 63424

You can see the three different ways to get at the value in a cell here. Put a date in A1, and format it as you format it.

Public Sub test()
 Dim r As Range
 Set r = Range("a1")
 MsgBox ("Value:[" & r.Value & "] Value2:[" & r.Value2 & "] Text:[" & r.Text & "]")
End Sub

In your case you want the Text.

Upvotes: 2

ApplePie
ApplePie

Reputation: 8942

In VBA, you can declare a variable as type Date instead of String which will solve that part of the problem.

In Excel, you need to specify a custom number format to "yyMMdd". Excel will understand that this cell is a date from the value that is in it and extract the proper year, month and day and display it as you specified.

Upvotes: 0

Related Questions