long line of text to columns

Peterfc2

Active Member
Joined
Jan 2, 2004
Messages
350
L team away L team home L home lose
etc.

I need to split the text to columns at the L (space). That's an L and a space
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,144
Office Version
365
Platform
Windows
How about
=TRIM(MID(SUBSTITUTE($A2,"L",REPT(" ",500)),(COLUMN(A1))*500,500))
dragged to the right
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,666
Office Version
2010
Platform
Windows
I need to split the text to columns at the L (space). That's an L and a space
Use Excel's Replace dialog to the two characters "L " (L and a space) with a character that is guaranteed not to be in the text of the cells and then use Text To Columns to split the text on that replacement character.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,453
Messages
5,414,627
Members
403,536
Latest member
JEduardo

This Week's Hot Topics

Top