Home

Looking for an excel formula that lower cases all letters up to ;

%3CLINGO-SUB%20id%3D%22lingo-sub-744560%22%20slang%3D%22en-US%22%3ELooking%20for%20an%20excel%20formula%20that%20lower%20cases%20all%20letters%20up%20to%20%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744560%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20an%20excel%20formula%20that%20lower%20cases%20all%20letters%20up%20to%20%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20biGtReeL%40BiGtree.com%3BFgA123456789%3C%2FP%3E%3CP%3EIn%20this%20case%2C%20all%20the%20letters%20up%20to%20%22%3B%22%20should%20be%20lower%20cased.%20Similar%20to%26nbsp%3B%3DREPLACE(A1%2C1%2C1%2CLOWER(LEFT(A1)))%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-744560%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744629%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20an%20excel%20formula%20that%20lower%20cases%20all%20letters%20up%20to%20%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744629%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373277%22%20target%3D%22_blank%22%3E%40easycreates%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20should%20work%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLOWER(LEFT(A1%2CMAX((EXACT(MID(A1%2CROW(INDIRECT(%22A1%3AA%22%26amp%3BLEN(A1)))%2C1)%2C%22%3B%22))*ROW(INDIRECT(%22A1%3AA%22%26amp%3BLEN(A1))))))%26amp%3BMID(A1%2CMAX((EXACT(MID(A1%2CROW(INDIRECT(%22A1%3AA%22%26amp%3BLEN(A1)))%2C1)%2C%22%3B%22))*ROW(INDIRECT(%22A1%3AA%22%26amp%3BLEN(A1))))%2B1%2CLEN(A1))%3C%2FP%3E%3CP%3EThis%20is%20and%20array%20formula%20and%20needs%20to%20be%20entered%20with%20Shift%2C%20Control%20Enter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744647%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20an%20excel%20formula%20that%20lower%20cases%20all%20letters%20up%20to%20%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744647%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373277%22%20target%3D%22_blank%22%3E%40easycreates%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%3E%3DSUBSTITUTE(A1%2CLEFT(A1%2CSEARCH(%22%3B%22%2CA1))%2CLOWER(LEFT(A1%2CSEARCH(%22%3B%22%2CA1))))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
easycreates
Occasional Visitor

Hello,

I am looking for an excel formula that lower cases all letters up to ;

For example, biGtReeL@BiGtree.com;FgA123456789

In this case, all the letters up to ";" should be lower cased. Similar to =REPLACE(A1,1,1,LOWER(LEFT(A1)))

Thanks.

2 Replies

Hi @easycreates 

 

This formula should work for you.

 

=LOWER(LEFT(A1,MAX((EXACT(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),";"))*ROW(INDIRECT("A1:A"&LEN(A1))))))&MID(A1,MAX((EXACT(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),";"))*ROW(INDIRECT("A1:A"&LEN(A1))))+1,LEN(A1))

This is and array formula and needs to be entered with Shift, Control Enter

@easycreates 

As variant

=SUBSTITUTE(A1,LEFT(A1,SEARCH(";",A1)),LOWER(LEFT(A1,SEARCH(";",A1))))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies