Results 1 to 9 of 9

How do I insert delimiter characters into a text string?

This is a discussion on How do I insert delimiter characters into a text string? within the Excel Questions forums, part of the Question Forums category; I am making a sheet in Excel 2003 (Win XP) where the user can paste a string of up to ...

  1. #1
    New Member
    Join Date
    Aug 2008
    Posts
    8

    Default How do I insert delimiter characters into a text string?

    I am making a sheet in Excel 2003 (Win XP) where the user can paste a string of up to 100 letters like GCTTTAATAGTAACCACG (a DNA sequence). After each letter is in a separate field I associate a number to it by using a lookup table and the VLOOKUP function. How can I get from GCTTTAATAGTAACCACG to |G|C|T|T|A|A|T|A|G|T|A|A|C|C|A|C|G|? I tried recording an Excel macro where I first insert commas as delimiters: G,C,T,T,T,A,A,T,A,G,T,A,A,C,C,A,C,G and use Text to Columns but this seems to hard code just this specific string into my macro. I made a macro in Word that inserts the commas, copied and pasted the delimited text into Excel and used Text to Columns. Is there a more elegant way of doing this in Excel?

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: How do I insert delimiter characters into a text string?

    try
    Code:
    Sub test()
    Dim myStr As String
    myStr = "GCTTTAATAGTAACCACG"
    MsgBox DNA(myStr)
    End Sub
     
    Function DNA(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "(.)"
        .Global = True
        DNA = Mid$(.replace(txt, "$|1"),2)
    End With
    End Function

  3. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,669

    Default Re: How do I insert delimiter characters into a text string?

    Jindon:

    Did you mean:
    DNA = Mid$(.replace(txt, "|$1"),2)

    but I think the op wanted the pipe symbol at the beginning and end too, perhaps that line could be:
    DNA = .Replace(txt, "|$1") & "|"
    Office 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: How do I insert delimiter characters into a text string?

    |G|C|T|T|A|A|T|A|G|T|A|A|C|C|A|C|G|?
    Missed...

  5. #5
    New Member
    Join Date
    Aug 2008
    Posts
    8

    Default Re: How do I insert delimiter characters into a text string?

    Thanks for your help! However, I need to explain much better:

    1. I want the user to paste a text string like GCTTTAATAGTAACCACG (different characters each time, variable length - up to 100) into cell B3. Wish I could show you my file!

    2. I need each character in a separate cell (in the same row): G in B4, C in C4, T in D4 and so on.

    3. In order to do so I thought of somehow inserting a delimiter character after each letter in the text string, a comma would be fine. This delimited text is now in B3.

    4. I made the VBA script below to copy B3 to B4 and use TextToColumns to distribute the delimited characters in B4 into B4:CW4.

    Sub Enter()
    '
    ' Enter Macro
    ' Macro recorded 8/28/2008 by Bernard Hane
    '
    ' Keyboard Shortcut: Ctrl+Shift+E
    '
    Range("B4:CW4").Select
    Selection.ClearContents
    Range("B3").Select
    Selection.Copy
    Range("B4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("B4").Select
    Selection.TextToColumns Destination:=Range("B4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
    ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
    (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
    Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
    33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
    Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array( _
    46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), _
    Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array( _
    59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), _
    Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array( _
    72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), _
    Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array( _
    85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), _
    Array(92, 1), Array(93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array( _
    98, 1), Array(99, 1), Array(100, 1)), TrailingMinusNumbers:=True
    Range("B4").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False

    End With
    End Sub

    This macro works fine but it requires the user to have comma-delimited text. Perhaps you know of a much better way to do this. Thanks again!

  6. #6
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: How do I insert delimiter characters into a text string?

    So user would enter text in B3 and the result shoud be in B4,C4....
    Delete existing code first.
    To a worksheet module
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x
    If Target.Address(0, 0) <> "B3" Then Exit Sub
    Application.EnableEvents = False
    Rows(4).ClearContents
    If Len(Target.Value) Then
        x = Split(DNA(Target.Value), "|")
        Range("b4").Resize(, UBound(x) + 1).Value = x
    End If
    Application.EnableEvents = True
    End Sub
     
    Private Function DNA(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "(.)"
        .Global = True
        DNA = Mid$(.Replace(txt, "|$1"), 2)
    End With
    End Function

  7. #7
    New Member
    Join Date
    Aug 2008
    Posts
    8

    Default Re: How do I insert delimiter characters into a text string?

    Thank you! I copied this into Module 1. When I try to run it it prompts for a Macro Name. How can I get this to run?

  8. #8
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: How do I insert delimiter characters into a text string?

    Quote Originally Posted by minimalist View Post
    Thank you! I copied this into Module 1. When I try to run it it prompts for a Macro Name. How can I get this to run?
    No, not to Module1
    Delete the code in Module1 and

    1) right click on the sheet tab - [ViewCode]
    2) paste the code onto the right pane
    3) click "x" to close the window
    it will run when you change B3

  9. #9
    New Member
    Join Date
    Aug 2008
    Posts
    8

    Default Re: How do I insert delimiter characters into a text string?

    Works like a charm! Thank you very much! I do not understand how your code works, except for Rows(4).ClearContents, since I do not know how to program....

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com