Extract Every Other Character from Cell

[Sense-A]

I need a formula that extracts every other character from a cell.

For example:

112233445566 778899,, aaBBcc##

Would become:

123456 789, aBc#

Any ideas?

Thanks!

 

[Gerald Higgins]

Just to be clear (maybe it doesn't matter) - are you wanting to take the characters in the ODD numbered positions, or the EVEN numbered positions ?

If I had to do this, I think what I would probably do is use Excel's Data, Text to Columns feature to break each string out into fixed width buckets, each 1 character wide in it's own column.

Then use some kind of concatenation formula to string alternate characters back together again, such as
=A1&C1&E1....

and so on.

 

[sandy666]

maybe with PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    remdup = Table.Distinct(split),
    transpose = Table.Transpose(remdup),
    merge = Table.CombineColumns(transpose,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"result")
in
    merge[/SIZE]

Column1		result
112233445566 778899,, aaBBcc##		123456 789,aBc#

 

[Eric W]

If you have the CONCAT function (Excel 365, not the CONCATENATE function), then try:

=CONCAT(MID(A1,ROW(INDIRECT("1:"&INT(LEN(A1)/2)+1))*2-1,1))

confirmed with Control+Shift+Enter. This will take the odd positions. Remove the red -1 to get the even positions. If you don't have CONCAT, you'll need either Gerald's idea, or VBA, or a lot of helper cells.

 

[Sense-A]

Just to be clear (maybe it doesn't matter) - are you wanting to take the characters in the ODD numbered positions, or the EVEN numbered positions ?

If I had to do this, I think what I would probably do is use Excel's Data, Text to Columns feature to break each string out into fixed width buckets, each 1 character wide in it's own column.

Then use some kind of concatenation formula to string alternate characters back together again, such as
=A1&C1&E1....

and so on.

Thank you for all the helpful responses.

I'm unfamiliar with powerquery, and my Office 2016 professional doesn't have concat or textjoin or other related formulas, so I went with Gerald's method with a few modifications.

Data is in cell A1.

I entered 1 3 5 7 9 in columns A B C D E and stretched them to the far right of the workbook.

Directly underneath, I entered Mid($A$1,B1,1) and stretched it across the workbook.
This gets the 1st, 3rd, 5th, 7th, etc characters into their own columns

Oddly, concatenate would not work with the large array, so I used VBA to combine all the many columns and dump them into a single, designated cell below.

Sub combineText()

Dim rng As Range
Dim i As String

For Each rng In Selection

i = i & rng

Next rng

Range("A15").Value = Trim(i)

End Sub

I highlighted A3 to ZZZ3 and ran the vba code and got my answer in cell A15


This was a multi-step solution and not as simple as a single formula. But it works for my one-time use purpose. Thank you to everyone who helped!

 

[Fluff]

Another slightly simpler option

Function SenseA(Cl As Range) As String
   Dim i As Long
   
   For i = 1 To Len(Cl) Step 2
      SenseA = SenseA & Cl.Characters(i, 1).Text
   Next i
End Function

Used in the sheet like
=SenseA(A2)