How To Split A Text Based On Two Special Characters

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

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?

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

---

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

---

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

---

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.

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

Sure. Thanks snb.

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

---

I came up with:

Code:

---

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

---

Those following this thread may want to check over in the MrExcel forum (see link in the OP's original message)... the OP described his ultimate need in more detail and I provided him with a solution to that (rather than the question he asked here) using the code I posted in Message #5 as a basis.