Help! Import text into Excel and split it into colunms based on the structure of the text

Hello guys,

Need your help with something

I would like to import lists into Excel and have a formula that could split the test based on the line structure. I'll explain:

this is the structure:
00:00 : Name - Unknown

I would like to import a bunch of rows into column F for example. And then to write a formula on column A, B & C so that on
Column A would contain 'Name'
Column B would contain 'Unknown'
Column C would contain '00:00'

Another example:
67:64 : My name here - Unknown text here (with some more info here)
Column A would contain:
My name here
Column B would contain:
Unknown text here (with some more info here)
Column C would contain:
67:64


Thanks a lot guys!

Regards,

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

please see attached excel file showing column F with the data and columns A B C.. I gave a manual example of how the result of the formula should be.

Regards,

With data in D1

in A1, B1 and C1 respectively

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D1,":","|",2),"-","|"),"|",REPT(" ",99)),100,99))

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D1,":","|",2),"-","|"),"|",REPT(" ",99)),199,99))

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D1,":","|",2),"-","|"),"|",REPT(" ",99)),1,99))

Originally Posted by JohnTopley

With data in D1

in A1, B1 and C1 respectively

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D1,":","|",2),"-","|"),"|",REPT(" ",99)),100,99))

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D1,":","|",2),"-","|"),"|",REPT(" ",99)),199,99))

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D1,":","|",2),"-","|"),"|",REPT(" ",99)),1,99))

You sir made my day!!! Thanks so much!

Originally Posted by sandy666

maybe it will work for you

A1: =MID(F1,9,SEARCH("-",F1)-10)
B1: =MID(F1,SEARCH("-",F1)+2,999)
C1: =LEFT(F1,5)

based on your example

Thank you as well friend for helping out

Originally Posted by sandy666

maybe it will work for you

A1: =MID(F1,9,SEARCH("-",F1)-10)
B1: =MID(F1,SEARCH("-",F1)+2,999)
C1: =LEFT(F1,5)

based on your example

Thank you as well friend for helping out

You are welcome.
If problem is resolved, add reputation star and mark thread as solved. Thank you