How to strip dashes out of numbers in a variable

[mikeburg]

How do you strip out dashes, convert numers to a text string, & assign it to a variable from the following?

monthendate=6-30-05

monthendname=monthendate (need the monthendname to be 63005)

Thank so very much.

mikeburg

[Bernie Deitrick]

mike (who apparently doesn't like capital letters)

Dim MonthEndDate As Date
Dim MonthEndName As String
MonthEndDate = DateValue("6-30-05")
MonthEndName = CStr(Format(MonthEndDate, "mdyy"))
MsgBox MonthEndName

HTH,
Bernie
MS Excel MVP


"mikeburg" <[email protected]> wrote in message
news:[email protected]...
>
> How do you strip out dashes, convert numers to a text string, & assign
> it to a variable from the following?
>
> monthendate=6-30-05
>
> monthendname=monthendate (need the monthendname to be 63005)
>
> Thank so very much.
>
> mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=387171
>

[[email protected]]

Bernie Deitrick wrote:
> mike (who apparently doesn't like capital letters)
>
> Dim MonthEndDate As Date
> Dim MonthEndName As String
> MonthEndDate = DateValue("6-30-05")
> MonthEndName = CStr(Format(MonthEndDate, "mdyy"))
> MsgBox MonthEndName
>
> HTH,
> Bernie
> MS Excel MVP
>

I think I must be being pretty dumb, but doesn't Format return a
string? Why use CStr? Plus, CStr rather idioticaly adds a space to
the left of the string, so if you do need to use it, you should use:
MonthEndName = LTrim(CStr(Format(MonthEndDate, "mdyy")))

Iain

[Bob Phillips]

myVar = Application.Substitute("6-30-05","-","")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> How do you strip out dashes, convert numers to a text string, & assign
> it to a variable from the following?
>
> monthendate=6-30-05
>
> monthendname=monthendate (need the monthendname to be 63005)
>
> Thank so very much.
>
> mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=387171
>

[Bernie Deitrick]

You're right that I didn't need it - but CStr doesn't add a space:

Dim myVal As Long
myVal = 2314
MsgBox Len(myVal) = Len(CStr(myVal))

Could you post code that shows that it does?

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message news:[email protected]...
>
>
> Bernie Deitrick wrote:
>> mike (who apparently doesn't like capital letters)
>>
>> Dim MonthEndDate As Date
>> Dim MonthEndName As String
>> MonthEndDate = DateValue("6-30-05")
>> MonthEndName = CStr(Format(MonthEndDate, "mdyy"))
>> MsgBox MonthEndName
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>
> I think I must be being pretty dumb, but doesn't Format return a
> string? Why use CStr? Plus, CStr rather idioticaly adds a space to
> the left of the string, so if you do need to use it, you should use:
> MonthEndName = LTrim(CStr(Format(MonthEndDate, "mdyy")))
>
> Iain
>

[mikeburg]

Thank you very much.

You all help a lot & I appreciate it!

mikeburg

[[email protected]]

Bernie Deitrick wrote:
> You're right that I didn't need it - but CStr doesn't add a space:
>
> Dim myVal As Long
> myVal = 2314
> MsgBox Len(myVal) = Len(CStr(myVal))
>
> Could you post code that shows that it does?
>
> HTH,
> Bernie
> MS Excel MVP
>

No, I was right the first time - I'm being pretty dumb. Str adds a
leading space, not CStr. My bad.

Iain