Unwind Alt Enter format in cell

[SimonHughes]

Hello, I have a spreadsheet where the originator has used the Alt+Enter key to have two lines of text in a cell - they are all in column A and there are 355 entries like this. Is there a way to unwind this format so it all appears as one line (from which I can performa text to column action). Many thanks

Main Building - Ground Floor - Above Entrance To Meeting Room 9
Hilton hotel - Heathrow - Ground Floor - Outside meeting room 7

 

[Snakehips]

@SimonHughes
Using CLEAN() should do it. Eg column C
Maybe you can use the idea in column E ?

AutoTraining.xlsm
	A	B	C	D	E	F	G
1	abc - bfgr - shetdg		abc - bfgr - shetdg		abc	bfgr	shetdg
Sheet7

Cell Formulas
Range	Formula
C1	C1	=CLEAN(A1)
E1:G1	E1	=TEXTSPLIT(TRIM(CLEAN(A1)),"-")
Dynamic array formulas.

HTH

 

[Snakehips]

E1 formula better to be -

=TRIM(TEXTSPLIT(CLEAN(A1),"-"))

 

[MARK858]

Press Ctrl + j in the Text to Columns Other box at the delimiter stage

 

[SimonHughes]

@SimonHughes
Using CLEAN() should do it. Eg column C
Maybe you can use the idea in column E ?

AutoTraining.xlsm
	A	B	C	D	E	F	G
1	abc - bfgr - shetdg		abc - bfgr - shetdg		abc	bfgr	shetdg
Sheet7

Cell Formulas
Range	Formula
C1	C1	=CLEAN(A1)
E1:G1	E1	=TEXTSPLIT(TRIM(CLEAN(A1)),"-")
Dynamic array formulas.

HTH

This works - both work and textsplit works best for this application, many thanks indeed indded

 

[SimonHughes]

Press Ctrl + j in the Text to Columns Other box at the delimiter stage

Hi Mark, that works too, many thanks for your help