Replace
 

I have the following code:

CMonth = (Replace(Cells(13, 4).Value, "*", "???"))

The only value that will be in Cells(13, 4) will be one of the 12 months.
What I'm trying to do is truncate the month to just the 1st 3 chars. So if
the month is January, CMonth = Jan. If it's October, CMonth = Oct. So on
and so forth. The way I have it set up it's not working as intended though.
What am I doing wrong?

Replace
 

Hi

Try this:

cMonth = MonthName(Month(Cells(13, 4)), True)

Regards,
Per

On 4 Aug., 20:34, Bishop wrote:
I have the following code:

CMonth = (Replace(Cells(13, 4).Value, "*", "???"))

The only value that will be in Cells(13, 4) will be one of the 12 months. *
What I'm trying to do is truncate the month to just the 1st 3 chars. *So if
the month is January, CMonth = Jan. *If it's October, CMonth = Oct. *So on
and so forth. *The way I have it set up it's not working as intended though. *
What am I doing wrong?

Replace
 

Ok thanks for the idea. This is what I had to do to make it work:

Dim Month As Integer
If Cells(13, 4).Value = "January" Then Month = 1
If Cells(13, 4).Value = "February" Then Month = 2
If Cells(13, 4).Value = "March" Then Month = 3
If Cells(13, 4).Value = "April" Then Month = 4
If Cells(13, 4).Value = "May" Then Month = 5
If Cells(13, 4).Value = "June" Then Month = 6
If Cells(13, 4).Value = "July" Then Month = 7
If Cells(13, 4).Value = "August" Then Month = 8
If Cells(13, 4).Value = "September" Then Month = 9
If Cells(13, 4).Value = "October" Then Month = 10
If Cells(13, 4).Value = "November" Then Month = 11
If Cells(13, 4).Value = "December" Then Month = 12
CMonth = MonthName(Month, True)

But, I'm also trying to do the same thing with the year. The cell value is
going to be 2009, 2010, 2011, etc. I just need the last 2 digits. How do I
accomplish that?

"Per Jessen" wrote:

Hi

Try this:

cMonth = MonthName(Month(Cells(13, 4)), True)

Regards,
Per

On 4 Aug., 20:34, Bishop wrote:
I have the following code:

CMonth = (Replace(Cells(13, 4).Value, "*", "???"))

The only value that will be in Cells(13, 4) will be one of the 12 months.
What I'm trying to do is truncate the month to just the 1st 3 chars. So if
the month is January, CMonth = Jan. If it's October, CMonth = Oct. So on
and so forth. The way I have it set up it's not working as intended though.
What am I doing wrong?

Replace
 

If you just need the first three characters for the month name, try:

Dim cMonth As String
cMonth = Left(Sheet3.Cells(13, 4).Value, 3)

assuming it's okay to use a string for whatever purposes you have.

Replace
 

HI

I assumed you had a date in the cell. As you have a month name in the
cell you can just use:

cMonth=Left(cells(13,4),3)
cYear=right(cells(13,5),2) ' Change cell reference to suit.

Hopes this helps.
....
Per

On 4 Aug., 21:05, Bishop wrote:
Ok thanks for the idea. *This is what I had to do to make it work:

* * Dim Month As Integer
* * If Cells(13, 4).Value = "January" Then Month = 1
* * If Cells(13, 4).Value = "February" Then Month = 2
* * If Cells(13, 4).Value = "March" Then Month = 3
* * If Cells(13, 4).Value = "April" Then Month = 4
* * If Cells(13, 4).Value = "May" Then Month = 5
* * If Cells(13, 4).Value = "June" Then Month = 6
* * If Cells(13, 4).Value = "July" Then Month = 7
* * If Cells(13, 4).Value = "August" Then Month = 8
* * If Cells(13, 4).Value = "September" Then Month = 9
* * If Cells(13, 4).Value = "October" Then Month = 10
* * If Cells(13, 4).Value = "November" Then Month = 11
* * If Cells(13, 4).Value = "December" Then Month = 12
* * CMonth = MonthName(Month, True)

But, I'm also trying to do the same thing with the year. *The cell value is
going to be 2009, 2010, 2011, etc. *I just need the last 2 digits. *How do I
accomplish that?



"Per Jessen" wrote:
Hi

Try this:

cMonth = MonthName(Month(Cells(13, 4)), True)

Regards,
Per

On 4 Aug., 20:34, Bishop wrote:
I have the following code:

CMonth = (Replace(Cells(13, 4).Value, "*", "???"))

The only value that will be in Cells(13, 4) will be one of the 12 months. *
What I'm trying to do is truncate the month to just the 1st 3 chars. *So if
the month is January, CMonth = Jan. *If it's October, CMonth = Oct. *So on
and so forth. *The way I have it set up it's not working as intended though. *
What am I doing wrong?- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Replace
 

What is in D13... the full month name as text? If so, this one line will
replace all that code...

CMonth = Left(Cells(13, 4).Value, 3)

As for your question about the year, you didn't tell us what cell it is in
(I'm guessing it can't be in D13). Assuming it is in E13, try this...

CYear = Cells(13, 5).Value Mod 100

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
Ok thanks for the idea. This is what I had to do to make it work:

Dim Month As Integer
If Cells(13, 4).Value = "January" Then Month = 1
If Cells(13, 4).Value = "February" Then Month = 2
If Cells(13, 4).Value = "March" Then Month = 3
If Cells(13, 4).Value = "April" Then Month = 4
If Cells(13, 4).Value = "May" Then Month = 5
If Cells(13, 4).Value = "June" Then Month = 6
If Cells(13, 4).Value = "July" Then Month = 7
If Cells(13, 4).Value = "August" Then Month = 8
If Cells(13, 4).Value = "September" Then Month = 9
If Cells(13, 4).Value = "October" Then Month = 10
If Cells(13, 4).Value = "November" Then Month = 11
If Cells(13, 4).Value = "December" Then Month = 12
CMonth = MonthName(Month, True)

But, I'm also trying to do the same thing with the year. The cell value
is
going to be 2009, 2010, 2011, etc. I just need the last 2 digits. How do
I
accomplish that?

"Per Jessen" wrote:

Hi

Try this:

cMonth = MonthName(Month(Cells(13, 4)), True)

Regards,
Per

On 4 Aug., 20:34, Bishop wrote:
I have the following code:

CMonth = (Replace(Cells(13, 4).Value, "*", "???"))

The only value that will be in Cells(13, 4) will be one of the 12
months.
What I'm trying to do is truncate the month to just the 1st 3 chars.
So if
the month is January, CMonth = Jan. If it's October, CMonth = Oct. So
on
and so forth. The way I have it set up it's not working as intended
though.
What am I doing wrong?

Replace
 

Thanks, Guys! I used Arjen Van and Your ideas for the Cmonth and Per
Jessen's idea for CYear. The following code works as intended:

CMonth = Left(Cells(13, 4).Value, 3)

CYear = Right(Cells(13, 7).Value, 2)

Thanks again.

"Rick Rothstein" wrote:

What is in D13... the full month name as text? If so, this one line will
replace all that code...

CMonth = Left(Cells(13, 4).Value, 3)

As for your question about the year, you didn't tell us what cell it is in
(I'm guessing it can't be in D13). Assuming it is in E13, try this...

CYear = Cells(13, 5).Value Mod 100

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
Ok thanks for the idea. This is what I had to do to make it work:

Dim Month As Integer
If Cells(13, 4).Value = "January" Then Month = 1
If Cells(13, 4).Value = "February" Then Month = 2
If Cells(13, 4).Value = "March" Then Month = 3
If Cells(13, 4).Value = "April" Then Month = 4
If Cells(13, 4).Value = "May" Then Month = 5
If Cells(13, 4).Value = "June" Then Month = 6
If Cells(13, 4).Value = "July" Then Month = 7
If Cells(13, 4).Value = "August" Then Month = 8
If Cells(13, 4).Value = "September" Then Month = 9
If Cells(13, 4).Value = "October" Then Month = 10
If Cells(13, 4).Value = "November" Then Month = 11
If Cells(13, 4).Value = "December" Then Month = 12
CMonth = MonthName(Month, True)

But, I'm also trying to do the same thing with the year. The cell value
is
going to be 2009, 2010, 2011, etc. I just need the last 2 digits. How do
I
accomplish that?

"Per Jessen" wrote:

Hi

Try this:

cMonth = MonthName(Month(Cells(13, 4)), True)

Regards,
Per

On 4 Aug., 20:34, Bishop wrote:
I have the following code:

CMonth = (Replace(Cells(13, 4).Value, "*", "???"))

The only value that will be in Cells(13, 4) will be one of the 12
months.
What I'm trying to do is truncate the month to just the 1st 3 chars.
So if
the month is January, CMonth = Jan. If it's October, CMonth = Oct. So
on
and so forth. The way I have it set up it's not working as intended
though.
What am I doing wrong?