How To Split A Text Based On Two Special Characters

[Charles]

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



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


to



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-questions/803835-split-sting-baised-2-characters.html#post3930583

[Excel Fox]

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]

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.




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]

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]

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

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]

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]

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

[Excel Fox]

Sure. Thanks snb.

[Charles]

Hi,

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



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]

I came up with:



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

[Rick Rothstein]

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.