Split string without delimiters

Takiar

New Member
Joined
Mar 13, 2022
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi,

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

Book3.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1MIINHNTSAINASRNNGINAANLMIINHNTSAINASRNNGINAANL  
Sheet1044
Cell Formulas
RangeFormula
B1:Z1B1=MID($A1,COLUMNS($B1:B1),1)
 
Upvote 0
Another option as you have xl 365
Excel Formula:
=MID(A1,SEQUENCE(,LEN(A1)),1)
 
Upvote 0
Solution
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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
A vba approach

VBA Code:
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1SequenceResult Cells:
2MIINHNTSAINASRNNGINAANL23 chars:MIINHNTSAINASRNNGINAANL
3EAAPMNHGHEILQEHRLVWLSWS23 chars:EAAPMNHGHEILQEHRLVWLSWS
4KYQEIWDINLPFFFVIKTILYTQEYMGLGN30 chars:KYQEIWDINLPFFFVIKTILYTQEYMGLGN
5GYWVLHVAHVWNNYGQDVLLFTD23 chars:GYWVLHVAHVWNNYGQDVLLFTD
6TKGDTIRAQWIWYWILTDSHKPH23 chars:TKGDTIRAQWIWYWILTDSHKPH
7NGRYNIAFVDFPGIVTHPKAINQYNLDYCA30 chars:NGRYNIAFVDFPGIVTHPKAINQYNLDYCA
8WTNHKFEQIDTACNNSMTCYKNA23 chars:WTNHKFEQIDTACNNSMTCYKNA
Sheet3
Cell Formulas
RangeFormula
B2:B8B2=LEN(A2) & " chars:"
 
Upvote 0
A vba approach
The nested For..Next loop can be eliminated, and your macro condensed by doing it this way...
VBA Code:
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.
 
Upvote 0
Another VBA approach.

Sequences II.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Sequence
2MIINHNTSAINASRNNGINAANLMIINHNTSAINASRNNGINAAN
3EAAPMNHGHEILQEHRLVWLSWSEAAPMNHGHEILQEHRLVWLSW
4KYQEIWDINLPFFFVIKTILYTQEYMGLGNKYQEIWDINLPFFFVIKTILYTQEYMGLG
5GYWVLHVAHVWNNYGQDVLLFTDGYWVLHVAHVWNNYGQDVLLFT
6TKGDTIRAQWIWYWILTDSHKPHTKGDTIRAQWIWYWILTDSHKP
7NGRYNIAFVDFPGIVTHPKAINQYNLDYCANGRYNIAFVDFPGIVTHPKAINQYNLDYC
8WTNHKFEQIDTACNNSMTCYKNAWTNHKFEQIDTACNNSMTCYKN
Sheet4
Cell Formulas
RangeFormula
B8:W8,B7:AD7,B5:W6,B4:AD4,B2:W3B2=OCHAR(A2)
Dynamic array formulas.


VBA Code:
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
 
Upvote 0
Thanks for all the VBA approaches as well :)

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

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top