Forum Discussion

BethP107's avatar
BethP107
Copper Contributor
Jan 17, 2024

EXCEL - How to create columns of data from one cell containing a semi-colon between multiple words.

I have a string of data like this in one cell.

mailto:email address removed for privacy reasons;Cochran;Ralph;;02-17-2017;01-17-2024

I need each piece of the cell that is separated by a semi-colon put into a separate column, instead of all three pieces of data all in one column.

excel
Formulas and Functions
office 365

3 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 17, 2024

    BethP107 

    =IFNA(DROP(REDUCE("",SEQUENCE(ROWS(A2:A4)),LAMBDA(u,v,VSTACK(u,TEXTSPLIT(INDEX(A2:A4,v),";")))),1),"")

     

    With Office 365 or Excel for the web you can apply this formula.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 17, 2024

    Hi BethP107 

     

    One way => https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

    Another if you run 365 => https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7 function

Resources