Split small letters from a text string

[AAP]

Hi,

I would like to separate small letters from a text string.
Ex:

Text String: ED276Ubiipx

Result Needed: biipx

I have attached a sample file.

 

[chirayu]

https://excelribbon.tips.net/T009091_Splitting_Cells_by_Case.html

 

[bosco_yip]

Try,

In B2, Copied down :

=MID(A2,MATCH(2,INDEX(1/(CODE(MID(A2,ROW($1:$99),1))<96),0))+1,99)

Regards
Bosco

 

[AAP]

Try,

In B2, Copied down :

=MID(A2,MATCH(2,INDEX(1/(CODE(MID(A2,ROW($1:$99),1))<96),0))+1,99)

Regards
Bosco

Many many thanks Bosco.
I am grateful to you.

 

[rahulshewale1]

Hi @AAP ,

Or use flashfill option .

type desire result entry 3 rows and select range B2 : B50 and press shortcut key CTRL + E


Regard
Rahul shewale

 

[GraH - Guido]

I came up with this Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "TEXT", "TEXT - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "TEXT - Copy", Splitter.SplitTextByCharacterTransition({"A".."Z"}, {"a".."z"}), {"TEXT - Copy.1", "TEXT - Copy.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "TEXT - Copy.1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"TEXT - Copy.1.1", "TEXT - Copy.1.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Split Column by Character Transition1", "Extract LowerCase", each if [#"TEXT - Copy.2"] = null then [#"TEXT - Copy.1.2"] else [#"TEXT - Copy.2"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"TEXT - Copy.1.1", "TEXT - Copy.1.2", "TEXT - Copy.2"})
in
    #"Removed Columns"

Not sure it would cover all cases though.

EDIT: Slightly improved code could be (used in attached file)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "TEXT", "TEXT - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "TEXT - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"TEXT - Copy.1", "TEXT - Copy.2"}),
    #"Added Prefix" = Table.TransformColumns(#"Split Column by Character Transition", {{"TEXT - Copy.2", each "A" & _, type text}}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Added Prefix", "TEXT - Copy.2", Splitter.SplitTextByCharacterTransition({"A".."Z"}, {"a".."z"}), {"TEXT - Copy.2.1", "TEXT - Copy.2.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"TEXT - Copy.1", "TEXT - Copy.2.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"TEXT - Copy.2.2", "Extract"}})
in
    #"Renamed Columns"