Text to Columns - Single Split

%3CLINGO-SUB%20id%3D%22lingo-sub-3328839%22%20slang%3D%22en-US%22%3EText%20to%20Columns%20-%20Single%20Split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3328839%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20split%20by%20SPACE%2C%20but%20only%20the%20first%20time%3F%3C%2FP%3E%3CP%3E---%3C%2FP%3E%3CP%3EExample%20Cells%20in%20a%20Column%3A%3C%2FP%3E%3CP%3EJoe%20likes%20books.%3C%2FP%3E%3CP%3EBob%20like%20to%20eat%20shoes.%3C%2FP%3E%3CP%3ERon%20likes%20the%20sound%20of%20crunchy%20cookies.%3C%2FP%3E%3CP%3E---%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20want%20these%20cells%20split%20so%20that%20the%20column%20contains%20Joe%2C%20Bob%2C%20Ron.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESince%20I%20don't%20care%20about%20the%20additional%20data%2C%20I%20don't%20want%20multiple%20additional%20columns%20deleting%20data%20from%20other%20columns.%20Or%20the%20tedious%20counting%20%5Bmax%5D%20needed%20columns%20to%20insert%2C%20in%20order%20to%20make%20room%20for%20the%20Text%20to%20Columns%20replacing%20split.%3C%2FP%3E%3CP%3EIt'd%20be%20nice%20to%20insert%20one%20column%2C%20and%20then%20conditional%20split%2C%20and%20stop%20after%20first%20delimiter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20is%20there%20a%20much%20easier%2Ffaster%20way%20to%20get%20my%20desired%20result%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20ahead%20of%20time%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Tony%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3328839%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3328883%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20-%20Single%20Split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3328883%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1376529%22%20target%3D%22_blank%22%3E%40DraygonTech%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20use%20a%20combo%20formula%20using%20LEFT%20and%20FIND%20to%20achieve%20this.%20See%20below%20for%20my%20example%20formula.%20B2%20would%20be%20the%20cell%20containing%20%22Joe%20likes%20books%22%20for%20example.%20Then%20simply%20copy%20this%20formula%20down%20the%20column.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3DLEFT(B2%2C%20(FIND(%22%20%22%2C%20B2%2C%201)-1))%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20if%20this%20works%20for%20you.%20Best%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello All,

 

Is there a way to split by SPACE, but only the first time?

---

Example Cells in a Column:

Joe likes books.

Bob like to eat shoes.

Ron likes the sound of crunchy cookies.

---

I want these cells split so that the column contains Joe, Bob, Ron.

Since I don't care about the additional data, I don't want multiple additional columns deleting data from other columns. Or the tedious counting [max] needed columns to insert, in order to make room for the Text to Columns replacing split.

It'd be nice to insert one column, and then conditional split, and stop after first delimiter.

 

Or is there a much easier/faster way to get my desired result?

 

Thanks ahead of time,

 

- Tony

1 Reply

Hi @DraygonTech,

 

You could use a combo formula using LEFT and FIND to achieve this. See below for my example formula. B2 would be the cell containing "Joe likes books" for example. Then simply copy this formula down the column.

 

=LEFT(B2, (FIND(" ", B2, 1)-1))

 

Let me know if this works for you. Best,