How to capitalize everything before a certain character?

Burrgogi

Board Regular
Joined
Nov 3, 2005
Messages
236
In column A, I have some text:
Hello good day.mp3
Hello good day.flac
etc.

I'd like to capitalize everything before the period. I don't need the file extension capitalized.

I have a feeling that I need to combine the PROPER function with the FIND function but I'm struggling to find the right formula. I'm lost. :(
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
Can you clarify something for me (us)? When you say "capitalize everything before the period", do you mean all letters or just the first letter of each word? I ask because you mentioned the PROPER function which does the latter.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,789
Office Version
2013
Platform
Windows
Don't have Excel but try
Rich (BB code):
=UPPER(LEFT(A1,FIND(".",A1,1)))&MID(A1,FIND(".",A1)+1,10)
 

Burrgogi

Board Regular
Joined
Nov 3, 2005
Messages
236
Can you clarify something for me (us)? When you say "capitalize everything before the period", do you mean all letters or just the first letter of each word? I ask because you mentioned the PROPER function which does the latter.
I must admit I worded that rather poorly. I should have said capitalize each word It's been a very long day for me, full of yard work. :(

The end result I'm trying to achieve is this:

Hello Good Day.mp3
Hello Good Day.flac
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
627
Office Version
2016
Platform
Windows
Hi Burrgogi,

Is this what you wanted?

Book1
AB
1Hello good day.mp3
HELLO GOOD DAY.mp3
2Hello good day.flac
HELLO GOOD DAY.flac
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=UPPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
627
Office Version
2016
Platform
Windows
Ah! I just saw your clarification.

Book1
AB
1Hello good day.mp3
Hello Good Day.mp3
2Hello good day.flac
Hello Good Day.flac
Sheet1 (2)
Cell Formulas
RangeFormula
B1:B2B1=PROPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,789
Office Version
2013
Platform
Windows
Ok, change to
Rich (BB code):
=Proper(LEFT(A1,FIND(".",A1,1)))&MID(A1,FIND(".",A1)+1,10)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
Another question (your answer might affect the function of the formulas you have received)... is it possible for there to be period (say, on an abbreviation perhaps) appearing before the period in front of the file extension?
 

Burrgogi

Board Regular
Joined
Nov 3, 2005
Messages
236
Another question (your answer might affect the function of the formulas you have received)... is it possible for there to be period (say, on an abbreviation perhaps) appearing before the period in front of the file extension?
Hi Rick, there are no other periods that appear in the filename. I have double checked.

Since trying out Toadstool's formula, I have discovered 2 unexpected problems:

#1.
I have apostrophies appearing in some of the filenames. For example, using his formula makes turns this filename:
He's Funny That Way.mp3

Into this:
He'S Funny That Way.mp3


#2. The PROPER function is kind of mucking up the works a bit. It's converting second letter (actually all letters after the 1st one) in these particular scenarios lowercase which is definitely not what I want.
III Some Filename.mp3
XI Kyrie Eleison.mp3

Becomes this:
Iii Some Filename.mp3
Xi Kyrie Eleison.mp3

Any thoughts?
 

Watch MrExcel Video

Forum statistics

Threads
1,096,187
Messages
5,448,860
Members
405,535
Latest member
KLFT

This Week's Hot Topics

Top