Split string without delimiters

[Takiar]

Greetings,

I am analyzing protein sequences, and wanted to use Excel's tools to facilitate the analysis. For that, I wish that all amino acids (1-letter abbreviation) occupies its own cell. The main problem is that the sequences come without any delimiter, and they can be quite long (hundreds if not a couple thousand characters).

I'm sure there is a way to split the 1 cell into 336 (example in front of me right now) cells, perhaps using VBA (with which I'm not familiar).

The example: Cell A1 has this string: MIINHNTSAINASRNNGINAANL
How can I make cells A2->... each include 1 character (here, a 23-character string becomes 23 cells of 1 character).

There would be many more sequences, much longer than this example here, so I'd love a way to automate this as I enter the sequences.

Thanks for the help!

 

[jtakw]

Hi,

This is one way, using formula, B1 formula copied across as far as needed:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	MIINHNTSAINASRNNGINAANL	M	I	I	N	H	N	T	S	A	I	N	A	S	R	N	N	G	I	N	A	A	N	L
Sheet1044

Cell Formulas
Range	Formula
B1:Z1	B1	=MID($A1,COLUMNS($B1:B1),1)

 

[Fluff]

Another option as you have xl 365

=MID(A1,SEQUENCE(,LEN(A1)),1)

 

[Takiar]

Thanks for you reply!

I was looking too complicated I guess

I must admit I was hoping for a bit more dynamic solution, which might have made the file use less memory (it's going to be big), and made it easier to carry over tabs and sheets without having to copy all those cells, or the whole tab.

 

[Takiar]

Another option as you have xl 365

=MID(A1,SEQUENCE(,LEN(A1)),1)

Even better !

Thanks !

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[rlv01]

A vba approach

Sub ExpandSequence()
    Dim WS As Worksheet
    Dim RangeOfCells As Range
    Dim R As Range
    Dim I As Long
    Dim Ch As String

    Set WS = ActiveSheet
    Set RangeOfCells = WS.Range("A2:A" & WS.Range("A" & WS.Rows.Count).End(xlUp).Row)

    For Each R In RangeOfCells
        For I = 1 To Len(Trim(R.Value))
            Ch = Mid(Trim(R.Value), I, 1)
            R.Offset(0, I + 1).Value = Ch
        Next I
    Next R
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Sequence	Result Cells:
2	MIINHNTSAINASRNNGINAANL	23 chars:	M	I	I	N	H	N	T	S	A	I	N	A	S	R	N	N	G	I	N	A	A	N	L
3	EAAPMNHGHEILQEHRLVWLSWS	23 chars:	E	A	A	P	M	N	H	G	H	E	I	L	Q	E	H	R	L	V	W	L	S	W	S
4	KYQEIWDINLPFFFVIKTILYTQEYMGLGN	30 chars:	K	Y	Q	E	I	W	D	I	N	L	P	F	F	F	V	I	K	T	I	L	Y	T	Q	E	Y	M	G	L	G	N
5	GYWVLHVAHVWNNYGQDVLLFTD	23 chars:	G	Y	W	V	L	H	V	A	H	V	W	N	N	Y	G	Q	D	V	L	L	F	T	D
6	TKGDTIRAQWIWYWILTDSHKPH	23 chars:	T	K	G	D	T	I	R	A	Q	W	I	W	Y	W	I	L	T	D	S	H	K	P	H
7	NGRYNIAFVDFPGIVTHPKAINQYNLDYCA	30 chars:	N	G	R	Y	N	I	A	F	V	D	F	P	G	I	V	T	H	P	K	A	I	N	Q	Y	N	L	D	Y	C	A
8	WTNHKFEQIDTACNNSMTCYKNA	23 chars:	W	T	N	H	K	F	E	Q	I	D	T	A	C	N	N	S	M	T	C	Y	K	N	A
Sheet3

Cell Formulas
Range	Formula
B2:B8	B2	=LEN(A2) & " chars:"

 

[Rick Rothstein]

A vba approach

The nested For..Next loop can be eliminated, and your macro condensed by doing it this way...

Sub ExpandSequence()
    Dim R As Range, Arr As Variant
    For Each R In Range("A2", Cells(Rows.Count, "A").End(xlUp))
      Arr = Split(StrConv(R.Value, vbUnicode), Chr(0))
      R.Offset(, 1).Resize(, UBound(Arr) + 1) = Arr
    Next
End Sub

....perhaps using VBA (with which I'm not familiar).

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ExpandSequence) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[lrobbo314]

Another VBA approach.

Sequences II.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Sequence
2	MIINHNTSAINASRNNGINAANL	M	I	I	N	H	N	T	S	A	I	N	A	S	R	N	N	G	I	N	A	A	N
3	EAAPMNHGHEILQEHRLVWLSWS	E	A	A	P	M	N	H	G	H	E	I	L	Q	E	H	R	L	V	W	L	S	W
4	KYQEIWDINLPFFFVIKTILYTQEYMGLGN	K	Y	Q	E	I	W	D	I	N	L	P	F	F	F	V	I	K	T	I	L	Y	T	Q	E	Y	M	G	L	G
5	GYWVLHVAHVWNNYGQDVLLFTD	G	Y	W	V	L	H	V	A	H	V	W	N	N	Y	G	Q	D	V	L	L	F	T
6	TKGDTIRAQWIWYWILTDSHKPH	T	K	G	D	T	I	R	A	Q	W	I	W	Y	W	I	L	T	D	S	H	K	P
7	NGRYNIAFVDFPGIVTHPKAINQYNLDYCA	N	G	R	Y	N	I	A	F	V	D	F	P	G	I	V	T	H	P	K	A	I	N	Q	Y	N	L	D	Y	C
8	WTNHKFEQIDTACNNSMTCYKNA	W	T	N	H	K	F	E	Q	I	D	T	A	C	N	N	S	M	T	C	Y	K	N
Sheet4

Cell Formulas
Range	Formula
B8:W8,B7:AD7,B5:W6,B4:AD4,B2:W3	B2	=OCHAR(A2)
Dynamic array formulas.

Function OCHAR(s As String)
    With Application.WorksheetFunction
        OCHAR = .Index(Split(StrConv(s, vbUnicode), Chr(0)), 1, .Sequence(1, Len(s) - 1))
    End With
End Function

 

[Takiar]

Thanks for all the VBA approaches as well

It's definitely something I should learn some time...