Manipulatng text format

[Harmonica]

Is there a formula in Excel to convert text that has been written in capital letters e.g. "SPOCK", so that it is written as "Spock"?  Or for example, "ONE TWO THREE" into "One two three".

I see that LOWER turns everything into small letters, but I do not want that, as I want just the first letter unchanged.

Thanks!

John

[#2 · Accepted solution]

@Harmonica 

There's the function PROPER which could handle SPOCK, but not ONE TWO THREE because it takes each word and keeps the initial capital.  But if your ONE TWO THREE is in cell G8

then =LEFT(G8,1)&LOWER(RIGHT(G8,LEN(G8)-1))

will work.

[#3]

Thanks for the prompt response!

I need to look up what LEFT, RIGHT and LEN do as beyond my current knowledge.

Cheers!

John

[#4]

Looked up what those functions do...how clever you are!

The only bit I do not understand is the role of the ampersand (&).

JP

[#5]

@Harmonica 

The ampersand puts text strings together. It's a short cut. There's a function called CONCATENATE that can do the same thing. Look it up. There's a lot you can do with the various text manipulation strings.

However, if  you're going to be using Excel extensively, on thing I'd point out is that although you can use LEFT, MID and RIGHT to take strings apart, it's a lot easier to put separate ones together. So if you get to the point of creating databases of such things as names and addresses, always have separate fields for FNAME and LNAME (and any other related things such as PREFIX and SUFFIX). It's then easy to put them together as "Smith, John" OR "John Smith" and there will always be occasions when you want one or the other. It's also easier to sort into alphabetical order by last name.... Same with address---don't make it all one field.

[#6]

@Harmonica 

For your reading pleasure: here's a reference source on all of the text manipulation functions. You can tell that I'm old school, since I read here that CONCATENATE has been replaced by CONCAT.

https://www.excelfunctions.net/excel-text-functions.html

[#7]

Funnily enough, before posting my query here, I had just been using CONCAT.

Oh, and thanks once more...I was wondering what the difference was between CONCAT and CONCATENATE!

I shall presently be looking at that link you so kindly provided.

Thanks very much once more.

In the end, given that most of my list was fine to be manipulated with PROPER, I did that. For the few that needed lower cases after the first letter of the first word, I used your clever formula manipulation. All worked a treat!

All the best!

John