Solved: Text to columns (With only the first bit of text)

[chacanger]

Hi

I've recently been trying to split text into what text I need and what text I don't need through VBA to automate it. The text I receive comes from a web page so the text usually looks something like this...

Mr Chacanger</p><ul class="sp_pss"><li>29 replies in thread</li></ul><div>

But sometimes may look like...

Mr Fake Person</p><ul class="sp_pss">

So as you can see the text can change in terms of spaces, words and characters. When I do Text to columns I use separate by "<", but as I want only the Name part when I run the code it keeps the unwanted data e.g. </p><ul class="sp_pss">[/B] in columns next to it which is overwriting data in the columns next to it.

Does anyone know how to get around this? Also here is a sample of Code that does it to the active cell...

[VBA]Sub TTC()

Selection.TextToColumns _
Destination:=ActiveCell, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Other:=True, OtherChar:="<"
End Sub[/VBA]

Thanks

[scott56]

If you only want the name from the string and can ignore the rest of the text why not use the instr and mid or left function to extract the data that you need ?

You could do something like the following

[VBA]Function strExtractName(strInputString As String) As String
'Function will extract the name from a string
intEndOfNamePos = InStr(strInputString, "</")
strInputString = Mid(strInputString, 1, intEndOfNamePos)

End Function[/VBA]

[chacanger]

If you only want the name from the string and can ignore the rest of the text why not use the instr and mid or left function to extract the data that you need ?

You could do something like the following

[VBA]Function strExtractName(strInputString As String) As String
'Function will extract the name from a string
intEndOfNamePos = InStr(strInputString, "</")
strInputString = Mid(strInputString, 1, intEndOfNamePos)

End Function[/VBA]

Thanks Scott56, I took your advice and looked into those functions and implemented them into another piece of my code...

[VBA]Sub TEST()

Dim SearchString, SearchChar, MyPos, MyTrim
SearchString = ActiveCell ' String to search in.
SearchChar = "<" ' Search for "<".

' A textual comparison starting at position 4. Returns 6.
MyPos = InStr(4, SearchString, SearchChar, 1)
MyTrim = Left(SearchString, MyPos - 1)
ActiveCell.Value = MyTrim

End Sub [/VBA]

It works well, many thanks.