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
  • 239

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
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
  • 10,568