I am trying to extract the data from Line break to adjacent cell i have tried different formulas from online sources but all use some of delimiter but this one does not have any delimiter.

I hope someone can help me with this i would appreciate your help.

(661) 298-3070 
(661) 526-4640
(661) 250-9492
(661) 857-0317
(661) 857-0147
(661) 298-3070
(661) 891-1139

I want to keep these bracket () in when separated into adjacent cell. Looking for a formula to fix this,

Result would be like this:

CodePudding user response：

Using the text-to-columns feature, you should be able to complete using the following steps:

1. Select your data.
2. Open the text-to-columns feature from Data tab.
3. Choose "delimited" in step-1 of the wizard.
4. De-select "tab" delimiter and select only "other" as your type of delimiter.
5. Then next to the other input your cursor and press CTRL-J. The preview should be accurate and you're done.

CodePudding user response：

Try Using FILTERXML() Function

• Formula used in cell B2

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE($A2,CHAR(10),"</y><y>")&"</y></x>","//y"))

Or, If you are in MS365 and when writing the formula if Beta Channel is enabled then try using TEXTSPLIT()

• Formula used in cell B3

=TEXTSPLIT(A3,CHAR(10))

Edit,

Two more alternative approach using Formula,

• Formula used in cell B2

=IFERROR(FILTERXML("<x><y>"&SUBSTITUTE($A2,CHAR(10),"</y><y>")&"</y></x>","//y["&COLUMN(A1)&"]"),"")

Or,

• Formula used in cell B3

=TRIM(MID(SUBSTITUTE($A3,CHAR(10),REPT(" ",100)),COLUMN(A1)*100-99,100))