[Solved] Text to columns - split at first space

Discuss the spreadsheet application

[Solved] Text to columns - split at first space

Postby cul » Wed Jul 17, 2019 10:12 am

I need to split a single column at the first space only. Is this possible using text to column?

BJR005 Max Cilla Text to columns-> BJR005, Max Cilla
BJR006 Rocks & Waves Text to columns-> BJR006, Rocks & Waves
BJR007 Alain Peters Text to columns-> BJR007, Alain Peters

sorry, I couldn't work out how to paste a table or paste Html, so I used how it would look in a csv file.

 Edit: Change first space to "," would work too since then I could use text to column easily enough. One step extra, but that's fine for me! 
Last edited by robleyd on Thu Jul 18, 2019 12:40 pm, edited 3 times in total.
Reason: Add green tick
OpenOffice 3.1 on Windows 7
cul
 
Posts: 18
Joined: Mon Jan 29, 2018 1:15 pm

Re: Text to columns - split at first space

Postby Villeroy » Wed Jul 17, 2019 11:51 am

The first column is always 6 characters wide?
Switch to "fixed width" option and set one separator point at position 6.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26977
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text to columns - split at first space

Postby cul » Wed Jul 17, 2019 12:30 pm

Villeroy wrote:The first column is always 6 characters wide?
Switch to "fixed width" option and set one separator point at position 6.


No, it's not, but thank you. I didn't understand that's how fixed width worked.
OpenOffice 3.1 on Windows 7
cul
 
Posts: 18
Joined: Mon Jan 29, 2018 1:15 pm

Re: Text to columns - split at first space

Postby Villeroy » Wed Jul 17, 2019 12:52 pm

X1: =FIND(" ";A1) [position of first space]
Y1: =LEFT(A1;X1-1) [left of space]
Z1: =MID(A1;X1+1;LEN(A1)) [rest of string]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26977
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text to columns - split at first space

Postby gerard24 » Wed Jul 17, 2019 1:09 pm

=SUBSTITUTE(A1;" ";",";1)
replace 1st space by comma, then you can use "text to columns".
LibreOffice 6.2.4 on Windows 10
gerard24
Volunteer
 
Posts: 948
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: [solved] Text to columns - split at first space

Postby cul » Thu Jul 18, 2019 6:11 am

Thanks for the help both. Solved!
OpenOffice 3.1 on Windows 7
cul
 
Posts: 18
Joined: Mon Jan 29, 2018 1:15 pm


Return to Calc

Who is online

Users browsing this forum: mayfield and 27 guests