# Separating a Column of Patterned Cells

Occasional Contributor

# Separating a Column of Patterned Cells

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

3 Replies

# Re: Separating a Column of Patterned Cells

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)

# Re: Separating a Column of Patterned Cells

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"``````

# Re: Separating a Column of Patterned Cells

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)