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.ozgrid.com/forum/showthread.php?t=71459
http://www.mrexcel.com/forum/excel-q...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!

Cross-Post:
http://www.mrexcel.com/forum/excel-q...ormatting.html
http://www.ozgrid.com/forum/showthread.php?t=195819

[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

Please Login or Register  to view this content.

[Special-K]

In your pseudo-data you've defined both Place of Employment and Name in italics.
How are you going to separate those?
e.g.

Royal Bank of Canada Fred Bloggs
Bank of Montreal Bert Jones
Imperial Oil Harry Paul Smith

Each company has a different number of words making up their name so you can;t tell where the employee's name begins

[VBAIsHard]

In your pseudo-data you've defined both Place of Employment and Name in italics.
How are you going to separate those?
e.g.

Royal Bank of Canada Fred Bloggs
Bank of Montreal Bert Jones
Imperial Oil Harry Paul Smith

Each company has a different number of words making up their name so you can;t tell where the employee's name begins

Yes i do wish to seperate those and that was a typo in the original post. Thank you for noticing it. I have fixed it. The name, email and date all have normal formatting

[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]

Book1.xlsx

Sample data for anyone who wants to help. Thank you again

[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.

[JBeaucaire]

Since this is in the nonVBA section, some formulas to "extract" the needed strings.

D1: =LEFT(C1, FIND(".", C1)+2)
E1: =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," "))
F1: =TRIM(LEFT(SUBSTITUTE(MID(C1, FIND("- ", C1)+2, 1000)," ", REPT(" ", 1000)), 500))
G1: =MID(C1,FIND("[", C1), 20)

Copy those down as needed, hide column C if you wish, or highlight columns D:G and do a COPY > PASTESPECIAL: Values, then delete column C.

[VBAIsHard]

Since this is in the nonVBA section, some formulas to "extract" the needed strings.

D1: =LEFT(C1, FIND(".", C1)+2)
E1: =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," "))
F1: =TRIM(LEFT(SUBSTITUTE(MID(C1, FIND("- ", C1)+2, 1000)," ", REPT(" ", 1000)), 500))
G1: =MID(C1,FIND("[", C1), 20)

Copy those down as needed, hide column C if you wish, or highlight columns D:G and do a COPY > PASTESPECIAL: Values, then delete column C.

WOW! This is absolutly amazing! Thank you so much, i learned so much!!

[VBAIsHard]

Since this is in the nonVBA section, some formulas to "extract" the needed strings.

D1: =LEFT(C1, FIND(".", C1)+2)
E1: =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," "))
F1: =TRIM(LEFT(SUBSTITUTE(MID(C1, FIND("- ", C1)+2, 1000)," ", REPT(" ", 1000)), 500))
G1: =MID(C1,FIND("[", C1), 20)

Copy those down as needed, hide column C if you wish, or highlight columns D:G and do a COPY > PASTESPECIAL: Values, then delete column C.

If i have a cell containing: "word1 word2 word3"
Is there a way to split it on the second space so i could have: | word1 word2 | word3 |

Nevermind: https://support.office.com/en-ie/art...a-c35280be795d

[JBeaucaire]

D1: =LEFT(C1, FIND(".", C1)+2)
E1: =LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," ")), FIND(" ", TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," "))))
F1: =TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," ")), E1, ""))
G1: =TRIM(LEFT(SUBSTITUTE(MID(C1, FIND("- ", C1)+2, 1000)," ", REPT(" ", 1000)), 500))
H1: =MID(C1,FIND("[", C1), 20)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[VBAIsHard]

D1: =LEFT(C1, FIND(".", C1)+2)
E1: =LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," ")), FIND(" ", TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," "))))
F1: =TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(" -",C1)),D1,""),CHAR(160)," ")), E1, ""))
G1: =TRIM(LEFT(SUBSTITUTE(MID(C1, FIND("- ", C1)+2, 1000)," ", REPT(" ", 1000)), 500))
H1: =MID(C1,FIND("[", C1), 20)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thank you so much! I gave you reputation!
I asked this question on 3 sites and bumped all day and you're the only one who offered help, you're awesome! I marked it as solved

[popipipo]

I asked this question on 3 sites

Go to these sites and tell them you have a solution
They will appreciate that.