Need to select and copy characters after a : then paste them in adjacent cell

[yorkshirelad]

I felt sure there was a standard formula for this one but I can't find it. Any help would be most appreciated.

I have a series of cells that contain a question and an answer. At the end of each question is a colon ).

I need to copy the answers - that is the cell contents that come after the : and then paste the answers in the adjacent cell. I then need to leave just the question in the original cell.

Example

Cell A1 contains - "What's your dogs name: Paddy"
Cell A2 contains - "What's the capital of Italy: Rome"

I need to split that into

Cell A1 - "What's your dogs name:"
Cell B1 - "Paddy"
Cell A2 - "What's the capital of Italy:"
Cell B2 - "Rome"

One final point some of the answers may be blank, so the formula would need to set the calculated cell to also be blank.

Note - The quotes are included just to show cell contents.

Many thanks for any assistance, its much appreciated.

 

[mikerickson]

Have you looked at TextToColumns with a colon delimiter?

 

[VoG]

Try like this

Excel Workbook
	A	B	C
1	What's your dogs name: Paddy	What's your dogs name:	Paddy
a

 

[yorkshirelad]

Thanks VoG, that's exactly what I was looking for, I'm much obliged

 

[yorkshirelad]

Have you looked at TextToColumns with a colon delimiter?

Thanks Mike I'll also take a look at TextToColumns but the formula posted by VoG does the trick for now. It may be I can use your suggestion for another project

Many thanks