0

I want to make, in MS Excel, an equivalent of the MS Word Sentence case, to often be able to convert Excel text strings from all-caps to sentence case (without having to teach myself VBA).

My one-parameter formula to do this is =(LEFT(B3) & MID(LOWER(B3),2,LEN(LOWER(B3)))).

(I have seen Using Excel sheet as a function but that isn't quite what I'm seeking.)

Can I make a function that would allow me to simply reference the cell holding the all-caps string, e.g. =Sentence(B3) ?

Thank you for your thoughts/advice

iSeeker
  • 229
  • =(UPPER(LEFT(B3)) & LOWER(MID(B3,2,LEN(B3)))) – Prem Oct 21 '22 at 10:42
  • Thank you - I do know how to do that, Prem; my own formula is similar to yours. I'm looking for a way to be able to type something much shorter, e.g. =Sentence(cell ref), or even =Sent(ref) rather than the whole long formula. – iSeeker Oct 21 '22 at 10:46
  • 2
    No, passing a parameter to a user-defined function is only possible using a programming language. However, you can insert this long expression into a "named range" - select cell C3, press CTRL+F3, add a new range, give a name (for example, Sent) and the =UPPER(LEFT(B3;1)) & LOWER(MID(B3;2;LEN(B3))) formula. Now, wherever you write =Sent, the cell will work for the text from the cell on the left (as if you were calling Sent with the RC[-1] parameter) – JohnSUN Oct 21 '22 at 11:37
  • @JohnSUN Many thanks - will give that a go! – iSeeker Oct 21 '22 at 12:46

2 Answers2

2

If you are an Excel O365 subscriber, the LAMBDA() function is designed specifically to do exactly what you are attempting.

You would go into Name Manager and create a name Sentence. In the "Refers to" field you would enter:

=Lambda(a, LEFT(a) & MID(LOWER(a),2,LEN(LOWER(a))))

I'd personally simplify this to just:

=Lambda(a, LEFT(a) & LOWER(RIGHT(a,LEN(a)-1)))

In either case, =Sentence(B3) would deliver exactly the result you're seeking. But--this does require the newer functionality of O365 Excel. Your alternative if you are not an O365 customer is to define a macro, but that has implications that may be undesirable to your use case.

Max R
  • 427
  • That worked perfectly. Fortunately, I have recently become an O365 subscriber. Many thanks, Max. – iSeeker Oct 21 '22 at 16:32
  • 1
    @iSeeker I modified my answer a bit. LEN(LOWER(a)) should be identical in value to LEN(a), which eliminates an unnecessary function call. And I just used RIGHT() instead MID() in my second example. O365 has a whole range of functions that are life-changing if you are a frequent Excel user. I cannot overstate the impact of the changes. – Max R Oct 21 '22 at 17:44
  • @Max_R Thanks for that and for alerting me to https://bettersolutions.com/excel/functions/updates-365-new-functions.htm Excel , Max. – iSeeker Oct 21 '22 at 18:24
  • @Max_R I added a right parenthesis to my copy of your simplified formula, then worked perfectly. – iSeeker Oct 21 '22 at 18:44
  • @iSeeker Thanks, I fixed it! – Max R Oct 21 '22 at 19:20
0

=(LEFT(B3) & MID(LOWER(B3),2,LEN(LOWER(B3))))
in shorter form for ALL Excel-varints/versions
=LEFT(B3,1) & LOWER(MID(B3,2,1000))

  • The second parameter to LEFT() is mandatory.
  • The last (third) parameter to MID() may be any excessive number to keep as my characters from the string as available
    (curiosity fact, LibreOffice: POWER(2,31)-2 works, POWER(2,31)-1 does not).
  • The use of LOWER() is only necessary once.
Hannu
  • 9,289