How do I insert delimiter characters into a text string?

minimalist

New Member
Joined
Aug 28, 2008
Messages
9
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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") & "|"
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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....
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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