Text to Columns based on formatting

[VBAIsHard]

Hello very helpful people

I registered on this website because i saw you guys are extremely knowledgeable in excel.
I am just beginning VBA and i have encountered a problem i cannot solve.

I basically need text-to-column functionality delimited by formatting (Bold, Italics, normal)

I have done my research and used the search feature and found threads such as
http://www.mrexcel.com/forum/excel-questions/331765-split-cells-bold-text.html

but the results were not working for me.

My data is all in one column and is formatted like this
#. Country Place of Employment Name - email@email.com [Date]


What i want the result to be is each of these in their own column
| #. | Country | Place of Employment | Name | email@email.com | [Date]


I know how to do normal text-to-columns so the last few arnt a problem, but the first ones separated only by formatting is hard and i have no clue what to do.

Thank you in advance!

 

[VBAIsHard]

Bumping! Sorry, i really need to finish this formatting today. Does anyone know how to do this?

 

[VBAIsHard]

Cross-Post:
Text to Columns based on formatting
http://www.excelforum.com/excel-general/1094065-text-to-columns-based-on-formatting.html#post4133315

 

[VBAIsHard]

Can somebody please help me, i really need this

 

[VBAIsHard]

I have adapted this solution and i think it's partially working.

This seperates all the bold in A1 and then i change "Bold" to "Italic" and it separates all the italics character. I am almost at my goal.
Credit to stanleydgromjr

[COLOR=#333333]Option Explicit[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub SplitBold()
' stanleydgromjr, 11/21/2010, EF754022
Dim c As Range, a As Long, BS As String, NS As String
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  BS = "": NS = ""
  For a = 1 To Len(Trim(c)) Step 1
    If c.Characters(a, 1).Text = " " Then
      BS = BS & c.Characters(a, 1).Text
      NS = NS & c.Characters(a, 1).Text
    ElseIf c.Characters(a, 1).Font.FontStyle = "Bold" Then
      BS = BS & c.Characters(a, 1).Text
    ElseIf c.Characters(a, 1).Font.FontStyle <> "Bold" Then
      NS = NS & c.Characters(a, 1).Text
    End If
  Next a
  c.Offset(, 1) = Trim(BS)
  NS = Trim(NS)
  If Left(NS, 1) = Chr(150) Then NS = Right(NS, Len(NS) - 1)
  If Left(NS, 1) = Chr(45) Then NS = Right(NS, Len(NS) - 1)
  c.Offset(, 2) = Trim(NS)
Next c
Application.ScreenUpdating = True </code>[COLOR=#333333]End Sub[/COLOR]

 

[VBAIsHard]

Through the use of the above code in different configurations i have arrived with


| Country | Name of Employment | #. Name - Email@Email.com [Date] |


All in standard format. I think this has made the problem a lot easier. I just need to split up the last column (The first number field is useless), I just need to extract the name and email. They are separated by the "-" but the email could also have a "-" or a "." inside of it

 

[VBAIsHard]

[Update] Text to columns using space delimiter gets me REALLY close to the solution. Only things i cant do is control the people with 1 or 3 words in their name.
What could solve this is using the "-" in between the name and email to separate the two.