Extract from text string, separated by "**"

[c_radford]

Hi everyone,

I'm scratching my head as to how to resolve the problem of extracting text from a cell into a number of separate cells and I'm hoping you good people can help!

In cell A1, I have a lengthy text string separated by a number of asterisks ("**") and I need to separate the text between these asterisks out into individual cells. For example, in cell A1, my raw text string says:

Name ** Support Provided ** Job Title ** Day Worked ** Hours Worked ** Manager ** Other Detail.

I need to establish how to separate it out so that the Name Data appears in column B, the Support Provided data appears in column C, Job Title in Column D, and so on. The 'Name' data was easy enough to do using =(LEFT(a2,FIND(" **",a2)-1)) but I'm struggling with the rest. One issue which may cause problems is that there is not a set number of characters that appears between each set of asterisks.

I hope this makes sense - any assistance with this issue would be really appreciated as I've been trying to work out how to do it for ages but with little success!

Thanks!

 

[vaskov17]

TextToColumns with delimiter * should work. You will get some empty columns that way but those can be deleted

 

[VoG]

TextToColumns with delimiter * should work. You will get some empty columns that way but those can be deleted

Tick Treat consecutive delimiters as one to avoid empty columns.

 

[Rick Rothstein]

If you want a formula solution (so that you can change data in Column A without having to rerun Text To Columns, then put this formula in B2 and copy it across as many cells as you think the maximum number of fields there could ever be, then copy all those cells down as far as you think you will ever need...

=IF($A2="","",IF(COLUMN(A2)=1,LEFT($A2,FIND("**",$A2)-1),TRIM(MID(SUBSTITUTE($A2,"**",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN(A2)-1),LEN($A2)))))