Free Microsoft Excel 2013 Quick Reference

fix all caps text to first letter caps

I'm trying to get 36 big excel files (all 1,000 plus entries) ready to
combine into one file to publish to the web. Unfortunately, about half have
all caps, and the rest have first-letter of each word capitalized.

Is there a quick way to format all of these at once? If not at once, is
there just a quick way to fix the all caps to first letter in each workbook
individually?

Thanks,
MB


Post your answer or comment

comments powered by Disqus
How to convert (from all caps sentence) the first letter of the sententence
to Capital and the rest all in small - just like we write ordinary English
language. The 'Proper( )' function converts to Capital the first letter of
each word in the sentence. This does not serve the purpose.
--
Shrikant

I have a file with alot of columns of data. In a couple of the columns, the
words are typed with all caps. I would like the words to have only the first
letter of each word capitalized. I can not do an edit/replace because there
are many different words. Is there a formatting or edit/replace trick that I
can use to accomplish this? Any information is appreciated.

Thanks

I have a large worksheet in a database I am building and am transporting some
files that are all caps. Is there any possible way that I can change it to
small font except for the first letter of the word? Eventually I am going to
be creating mailing lists and feel it would be more presentable in this type
of format.

i want to change a list in all caps:
CHICAGO
SAN ANTONIO
SAN FRANCISCO

To a only the first letter cap:
Chicago
San Antonio
San Francisco

Hello,

I want to auto-cap's the "First Letter Of Every Word" in an excel cell.

I have already set up an Excel-VBA combo that converts a set of cell's contents to ALL CAPS, but a single cell "First Letter Only" would be cool for a different set of cells/cell.

I guess I could write 10 pages to "count left, find a 'space', set variable, caps that letter, + the right count,..." (or something like that, just a brain storm) ... haha but that is just too stupid, that would take a retarded amount of effort and code...

Any ideas ?

--------------------------
This is my first post on this forum so "HI all ya' locals ! I will tread lightly in your home ....."

Can you make ALL CAPS text change to lower case automatically, without
re-typing?
--
"You can have everything in life you want, if you will just help enough
other people get what they want." -- Zig Ziglar

Can you make ALL CAPS text change to lower case automatically, without
re-typing?
--
"You can have everything in life you want, if you will just help enough
other people get what they want." -- Zig Ziglar

I like the proper formula and find it handy, however do you know if there is a formula that would only keep the first letter in the cell at caps and reduce the rest to lower case, ie
A1 = MY DOG IS WHITE
proper(A1) = My Dog Is White
what I want is only first letter caps = My dog is white

I'va had a decent look around but can't find anything that would do this?
Cheers

I need to change several but not all cells from upper first letter, lower rest to all caps. Is there a way I can do this?

Hi,

I found the following code online that allows a user to force text to values in excel. This code works great except for a couple of issues. When I run the procedure, it coverts all my text to number within the sheet referenced, but it blows away my formulas and screws up my percentages.

Can someone help me modify this code so that it will only apply to what ever area I "highlight" on the sheet with my mouse? Or can you help me to fix the code so it does not blow away my formulas or messes up my percents. Thanks in advance for your brilliance.

Below is the current code that needs modification.

Sub ForceValueCode()

Dim LastPlace, Z As Variant, X As Variant, NewNumb
ActiveSheet.Select
LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
ActiveSheet.Range(Cells(1, 1), LastPlace).Select
Z = Selection.Address
For Each X In ActiveSheet.Range(Z)
If Len(X) > 0 Then
NewNumb = Application.Text(X, "0")
X.FormulaR1C1 = NewNumb
Else
X.FormulaR1C1 = ""
End If
Next
Range(Z).Select
Selection.NumberFormat = "0"
End Sub

I have a massive sheet listing musical artists by name, however they are in 'ALL CAPS', and I would like to change them to 'Just Initials Capitalized'.
Is there a way to do this fast?

I have a spreadsheet with many many drop-down lists. Is there any way
for a user to enter the first letter of an option and automatically
jump to that option in the list?

The goal is to make this faster to use than (or at least as fast as)
writing it out, and right now, clicking on, scrolling through, and
selecting from each drop-down is a lengthy process.

--
Siobhank
------------------------------------------------------------------------
Siobhank's Profile: http://www.excelforum.com/member.php...o&userid=26133
View this thread: http://www.excelforum.com/showthread...hreadid=394611

Hi All,

I have the following macro which I only recorded so it's fairly rough - my VBA coding is at a very low level. My problem is that the RefreshAll command works fine but then the Text to Columns operations do not work. The macro runs fine but the columns have not been converted to the General format that I'm looking for.

Does anyone have any advice on how to change the macro to ensure the Text to Columns works?

Sub Update_Database()
'
' Update_Database Macro
'

'
Sheets("Orders").Select
ActiveWorkbook.RefreshAll
Calculate
Sheets("Orders").Activate
Columns("A:A").Select
Sheets("Orders").Select
Selection.TextToColumns Destination:=Range( _
"Table_Flair_Database[[#Headers],[scode]]"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("Sales").Select
Selection.TextToColumns Destination:=Range( _
"Table_Flair_Database3[[#Headers],[scode]]"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("DNotes").Select
Selection.TextToColumns Destination:=Range( _
"Table_Flair_Database4[[#Headers],[scode]]"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("Orders").Select
End Sub

Thanks in advance
Nick

Hello all,

I've read this site without registering for some time, but now I have a question and am hoping someone can help me.

I have a book that has thousands of names all in ALL CAPS, and I was wondering if there was a way to convert them all to Proper Case? I also seem to be having trouble converting them one at a time using the =Proper formula.

Any thoughts.
Much appreciated.
RS

I have the following input:

First name: John Michael or John or Mary Jane Carmen
MI: C
Last name: Dela Toya or Doe or Roberts

Now what I need is to convert John Michael C Dela Toya to jmcdelatoya, or
John C Doe to jcdoe and mjcdroberts. I'm having trouble in making excel
count the number of words in a name, since if my input has 3 first names, it
has to have 3
initials,like this: Mary Jane Carmen C. Roberts to mjcdroberts. Can anyone
help me pls?

Mr. Biff gave me a formula, however the formula takes the FIRST TWO LETTERS
of the name and is not applicable with 3 or more word names. I need the first
letter of the name. The middle initial is ok, and the last name is already
fixed, I just need the FIRST LETTER of the NAME or NAMES. Thanks!

Hi - I have about 6000 rows of data, the first colunm is the site name. Data in the cells range from 12 characters to less than 8 characters. I would like to split the text in the first column based on the last 8 characters. I thought 'fixed width' in 'text to columns' would be worth persuing, but I need the last 8 characters (and fixed width starts from the front of the text) - anyone have any ideas?

I have a spreadsheet with many many drop-down lists. Is there any way for a user to enter the first letter of an option and automatically jump to that option in the list?

The goal is to make this faster to use than (or at least as fast as) writing it out, and right now, clicking on, scrolling through, and selecting from each drop-down is a lengthy process.

I have a long list of cells that all contain the text 'CN=User Name'. I
would like to remove all the text to the left of User Name leaving me with
just that. Is there a function that can do this for me and if not could I
and how would I go about doing it in VBA?

In cell E2 I have the following:
St Louis Cardinals vs Pittsburgh Pirates for Game

In cell E10 I have the following:
Chicago Cubs vs Los Angeles Dodgers for Game

I would like a formula in cell K2 to do the following:
Find the vs in cell E2 and then display all the text to the left of it (St
Louis Cardinals in this case). I included the cell E10 info so you can see
that the text to the left of the vs is not the same number of characters so I
can't just use the left formula. Any ideas? Thanks.

How do i get all text from a text file to worksheet without any formatting? Just import all the text to first cell. This must be done by VBA since i have lots of more code to write.

I have an excel shhet that is all caps and I would like to change only the
first letter to caps without retyping the whole sheet...is this possible?

Does anyone have a way to change columns of text entered in all caps to the initial capital letter and the rest of the text in small letters.

As in: JONES to Jones.

Thanks for any help you can give me.

Hook

I'm trying to convert data (a list of names) in a single column to all caps.
I've tried copying and pasting the text from Word, but the data doesn't
change.

I can't get the instructions in Excel help to fix it either....

ARG!!!!

Hi all,

I'm currently working for a company as a data entry admin. I'm asked to copy and paste some details from websites provided by the company and some websites have the addresses of the company in all caps.

My boss has asked me to change all these caps such that the first letter is caps, letters after "/" and "-" to caps, and the rest to small letters. He said that it would be very troublesome for him to do it himself 1 by 1, retyping them.

Obviously he don't know much about Excel. I did some research online and found this code.

Sub
ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
End Sub
This code successfully changes the first letter of each word to caps and the remaining letters to small letters, but I'm suffering from one problem.

Take for example I have an address that is

108-A SERANGOON ROAD.

This code changes it to

108-a Serangoon Road

My boss know nuts about excel, and I know nuts about these codes as I specialise in Java programming, so I'd like to seek your professional help in coding this code such that it will change letters that appear after "-" and "/" to caps as well.

I hope that's not too much to ask.


No luck finding an answer? You could always try Google.