Change the first letter from small cap to large cap

[m111]

Hi all,

I have column B which comprises of month and date : Jan 02-25. However, some of them are in small letters, eg : jan 02-25. That makes the sorting inaccurate. How to convert every first letter of the month in column B to capital letter?

Thank you so much for all the help that you have given! Indeed, Mr.Excel has helped me so much that I just want to say a big thank you!

m111

 

[Brian from Maui]

Hi all,

I have column B which comprises of month and date : Jan 02-25. However, some of them are in small letters, eg : jan 02-25. That makes the sorting inaccurate. How to convert every first letter of the month in column B to capital letter?

Thank you so much for all the help that you have given! Indeed, Mr.Excel has helped me so much that I just want to say a big thank you!

m111

I'm not sure how converting to caps affects sorting, but...

=proper(a1) where a1 contains jan 02-25

or

www.asap-utilities.com a free add-in that has a proper function

 

[Egress1]

Hi all,

I have column B which comprises of month and date : Jan 02-25. However, some of them are in small letters, eg : jan 02-25. That makes the sorting inaccurate. How to convert every first letter of the month in column B to capital letter?

Thank you so much for all the help that you have given! Indeed, Mr.Excel has helped me so much that I just want to say a big thank you!

m111

Ok not to be nit picky but how is "jan 02-25" a month and date? I would suggest you format column B to a DATE format then that should solve your sorting issue without having to change "j" to "J" etc.

 

[Barry Katcher]

OK, I'm assuming that your "dates" are actually text, not true dates. Given that:

1. The case of the first letter does not appear to affect the sorting. In other words, sorting does not seem to be case sensitive.

2. You can sort on your "dates" as is, but be aware that all dates that are Apr will be on top, followed by Aug, Dec, Feb, etc, and the last will be Sep. In other words, it will sort by the first letter of the month, not by the first true date of the year. This is fine if you just want to sort all dates within a month; doesn't work if you want to sort by true date.

3. Even if the "month" sort above is what you want, you must make sure all the entries are formatted the same, i.e. a three-letter month, followed by a space, followed by a two-number year, a dash, and a two number month.

I suggest, before we go any further, that you tell us exactly how you want to sort your list.

 

[m111]

Hi Barry,

I agree with what you say. The sort function really requires all the entries to be in proper order I encounter the same limitation in sort as you mentioned.

Sorry, I think it's my oversight. I want to add in rows after a certain comparison between the months. The current add-in rows function that I have does not differentiate between Jan and jan. That's why it compares and adds in a row between Jan 20-25 and jan 20-25. By changing the first letter of the month into big cap should have solved the problem, i believe.

So how should I change the first letter from small cap to big cap?

Thanks!

m111

 

[Barry Katcher]

OK, again assuming your entries are all text, and are in column B, you can use Brian's suggestion, above.

First, insert a new column between column B and C. Assuming your dates start in B1, in the new C1 enter =PROPER(A1). Copy it down as far as needed. You now have a column with the first letter in Capitals for every date.

Next, highlight column C and click Copy. Then, click Edit/Paste Special/ and choose Values. This changes the formulas to text. You can now delete column B, with the mixed cases, and use what is now the new column B, with the upper case.

 

[m111]

Barry,

Thanks very much for your help!

you are righ that the month doesn't sort from Jan-Dec. It sorts according to the first alphabelt.

My new problem here is that I have the later months and dates on top of the earlier months. Eg. June is on top of May. But I want to copy all the May data from the bottom sheet to the top and shows the June data at the bottom, immediately after a blank row from the May data. The thing is that Excel will overwrite the existing June that I have.

How should I prevent Excel from writing off the June data? The months and dates are again in column B as shown:

Core600 Jun 27-02
Core2500 Jun 27-02
Core150 Jun 27-02
500LP Jun 27-02

J150 May 01-05
J500 May 01-05
J150 May 01-05
J500 May 01-05
500LP May 01-05
600LP May 01-05

Thanks!

m111

 

[Brian from Maui]

m111,

You can insert the same number of rows you're trying to copy before you copy. Then paste, or you can copy and paste to a new worksheet.

 

[Egress1]

Barry,


Core600 Jun 27-02
Core2500 Jun 27-02
Core150 Jun 27-02
500LP Jun 27-02

J150 May 01-05
J500 May 01-05
J150 May 01-05
J500 May 01-05
500LP May 01-05
600LP May 01-05

Thanks!

m111

If I may (at the risk of being ignored again :wink: )

Book1.xls
	A	B	C	D
1	Core600	Jun27-02	6/27/1902
2	Core2500	Jun27-02	6/27/1902
3	Core150	Jun27-02	6/27/1902
4	500LP	May01-05	5/1/1905
5	J150	May01-05	5/1/1905
6	J500	May01-05	5/1/1905
7	J150	May01-05	5/1/1905
8	J500	May01-05	5/1/1905
9	500LP	May01-05	5/1/1905
10	600LP	May01-05	5/1/1905
australia

Insert a column format as DATE paste in the formulas all the way down then do your sort based on the inserted column then delete the column once your sort is done.

Wadya Think?

 

[Egress1]

Correction on that formula it should be

=DATE(2003,MONTH(LEFT(B1,6)),MID(B1,5,2))

This will force the years to be identical allowing you to sort by Just Month and Day