Separating a Column of Patterned Cells

[Carl_W]

I am working with a spreadsheet I've created from an imported text file.  So far, so good.  There is a column of data, however, that I'd like to separate into multiple columns.  The data is always in the form of a letter followed by one OR two digits, then another letter followed by one OR two digits (i.e., A#B#, A#B##, A##B#, or A##B##).  The first letter (A) in the sequence can vary.  The second (B) letter is always the same.  Unfortunately, when a number is a single digit, the text file does not make it "0#".  I think that would make this task easier as it would make all of the cells the same number of characters.

Starting with four columns next to the column of such cells, what is the best way to take the column of cells and divide the contents into four neighboring columns, A | ## | B | ## ?

Thanks in advance for the suggestions!

Carl

[#2]

@Carl_W 

Let's say the data are in A2 and down.

In B2: =LEFT(A2)

The following formulas are all array formulas, to be confirmed with Ctrl+Shift+Enter after each edit.

In C2: =--MID(A2,2,MATCH(FALSE,ISNUMBER(--MID(MID(A2,2,5),ROW($1:$5),1)),0)-1)

In D2: =MID(A2,MATCH(FALSE,ISNUMBER(--MID(MID(A2,2,5),ROW($1:$5),1)),0)+1,1)

In E2: =--MID(A2,MATCH(FALSE,ISNUMBER(--MID(MID(A2,2,5),ROW($1:$5),1)),0)+2,2)

 

[#3]

@Carl_W 

As variant Power Query could work with split text from non-digit to digit and opposite

Generated script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(
        Source,
        "Text",
        Splitter.SplitTextByCharacterTransition(
            (c) => not List.Contains({"0".."9"}, c),
            {"0".."9"}),
        {"A", "Text.2", "B.1"}
    ),
    #"Split Column by Character Transition1" = Table.SplitColumn(
        #"Split Column by Character Transition",
        "Text.2",
        Splitter.SplitTextByCharacterTransition(
            {"0".."9"},
            (c) => not List.Contains({"0".."9"}, c)
        ),
        {"A.1", "B"}
    )
in
    #"Split Column by Character Transition1"

[#4]

Thanks, guys! I ended up with the following (each formula in the neighboring column):
=LEFT(A13,1)
=IF(ISNUMBER(MID(A13,2,2)*1),MID(A13,2,2)*1,MID(A13,2,1)*1)
=IF(ISNUMBER(RIGHT(A13,3)*1),MID(A13,4,1)*1,MID(A13,3,1))
=IF(ISNUMBER(RIGHT(A13,2)*1),RIGHT(A13,2)*1,RIGHT(A13,1)*1)