How To Split A Text Based On Two Special Characters

**Charles** · 09-08-2014, 12:31 AM

Hi,

I can usually solve my problems. But, in this case I may be having a "CRAFT" moment.
What I'm trying to do is split a string form this

Code:

c.2339A>A/C; p.N780T

to

Code:

c.2339A>C

I know its something simple but...

Thank for any help.

I'm looking for VBA.

HI. I also have this posted on:

http://www.mrexcel.com/forum/excel-q...ml#post3930583

**Excel Fox** · 09-08-2014, 11:03 PM

Charles, the link doesn't seem to be pointing to the right thread

Anyway, your resultant text
c.2339A>C

seems to be different from what one would expect if it were a split with the semicolon (

So is there something beyond that, or is it just a typo?

**Excel Fox** · 09-08-2014, 11:31 PM

OK, got more information from the other thread regarding your problem. Can you paste the following code to a code module, and try calling the UDF function to solve your issue.

Code:

Function CSPLIT(strPassed As String) As String

    Dim str, strP As String
    strP = Split(strPassed, ";")(0)
    str = Split(strP, ">")(0) & ">"
    If InStr(1, Split(strPassed, ";")(0), "/") Then
        str = str & Split(Split(strPassed, ";")(0), "/")(1)
    Else
        str = str & Right(Split(strPassed, ";")(0), 1)
    End If
    CSPLIT = str
    
End Function

**snb** · 09-09-2014, 12:18 AM

Code:

sub M_snb()
   msgbox F_snb("c.639T>C; p.D213D")
end sub

function F_snb(c00)
  f_snb=split(split(c00,";")(0),"/")(0)
function end

**Rick Rothstein** · 09-09-2014, 12:34 AM

Here is a macro that does what I think you want...

Code:

Sub ParseMedicalCodes()
   With Range("A1", Cells(Rows.Count, "A").End(xlUp))
     .Copy .Offset(, 1)
     .Offset(, 1).Replace ";*", "", xlPart
     .Offset(, 1).Replace ">*/", ">", xlPart
   End With
 End Sub

**Excel Fox** · 09-09-2014, 08:46 AM

hi snb, could you check your formula again... it doesn't seem to be satisfying one of the requirements OP has mentioned in the other forum.

**snb** · 09-09-2014, 02:19 PM

I don't provide solutions, only suggestions for methods to attain a certain goal. The OP can adapt anything I suggest.

**Excel Fox** · 09-09-2014, 05:56 PM

Sure. Thanks snb.

**Charles** · 09-09-2014, 10:53 PM

Hi,

Thanks to all who helped.
With the help this is the final code that I came up with.

Code:

Sub Refseq()
Dim Mval As String
Dim i As Long
Dim lrow As Long
Sheets("Sheet2").Activate
lrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row
For i = 2 To lrow
    Range("G" & i) = Range("F" & i) & ":" & CSPLIT(Range("B" & i))
Next
End Sub

Function CSPLIT(strPassed As String) As String

    Dim str, strP As String
    strP = Split(strPassed, ";")(0)
    str = Split(strP, ">")(0) & ">"
    If InStr(1, Split(strPassed, ";")(0), "/") Then
        str = str & Split(Split(strPassed, ";")(0), "/")(1)
    Else
        str = str & Right(Split(strPassed, ";")(0), 1)
    End If
    CSPLIT = str
    
End Function

**snb** · 09-10-2014, 12:05 PM

I came up with:

Code:

Function F_snb(c00)
    F_snb = Split(c00, ">")(0) & ">" & Replace(Filter(Split(Replace(Replace(c00, ">", "/"), ";", "~/"), "/"), "~")(0), "~", "")
End Function