Moving text to a new column

[RebeccaF]

Hi sorry to be back with another question so quickly. I've been sent a spreadsheet with all the text for each row in column a. Each row starts with Line 145 (could be a different number put here): and then the text is beside it. What I've been asked to do is to keep Line (whatever number): in column a and then move the rest of the text to column b. I found I could do this easily enough by going into data, text to column, delimited, colon and finish. What I didn't realise though is that in some of the rows there is a second or third colon in the sentence. Is there an easy enough way for me to keep the line, number and colon in column a and keep the text in column b as when I do what I was doing earlier, it obvious separates the text into column c and d if there is another colon in it.
Thanks a million again and sorry if my explanation isn't the clearest!

 

[Joe4]

You could create formulas to do it.

So, if you have an entry in cell A1, put these formulas in:
cell B1: =LEFT(A1,FIND(":",A1)-1)
cell C1: =MID(A1,FIND(":",A1)+1,LEN(A1))
and copy down for all rows

 

[RebeccaF]

Thank you so much for that. I'll get the hang of these things eventually!

 

[Joe4]

You are welcome.

 

[lezawang]

Hi
I know your question was answered by Joe4 but if you like you can post a sample of your table here. There is an option in Power Query to tell which delimter should be used, the most left or most right etc. I am also interested to try your table using Power Query. Thanks