Home

Split data from one column without using text to column

%3CLINGO-SUB%20id%3D%22lingo-sub-183631%22%20slang%3D%22en-US%22%3ESplit%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183631%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20some%20data(Invoice%20Nos)%20in%20a%20col%20A%20in%20a%20text%20format%20separated%20with%22%2F%22%2C%20i%20want%20to%20split%20the%20same%20with%20using%20a%20formula%20(NOT%20TEXT%20TO%20COLUMN)%26nbsp%3Bcoz%20if%26nbsp%3B%3CSPAN%3Enew%20data%20is%20added%20I'll%20need%20to%20go%20through%20the%20operation%20again.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20tried%20a%20combination%20of%20LEN%2C%20FIND%20but%20didn't%20succeed%20to%20get%20the%20desired%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E7000478203%2F7000525492%2F7000532700%2F7000536756%2F7000562365%2F7000571920%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20do%20this%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E7000478203%3C%2FTD%3E%3CTD%3E7000525492%3C%2FTD%3E%3CTD%3E7000532700%3C%2FTD%3E%3CTD%3E7000536756%3C%2FTD%3E%3CTD%3E7000562365%3C%2FTD%3E%3CTD%3E7000571920%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-183631%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-185226%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-185226%22%20slang%3D%22en-US%22%3Ethen%20you%20use%20this%20code%20below.%3CBR%20%2F%3E%3CBR%20%2F%3Elets%20say%20your%20data%20is%20in%20Cell%20A1%20%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20Test()%3CBR%20%2F%3EDim%20X%20As%20Variant%3CBR%20%2F%3EX%20%3D%20Split(Range(%22A1%22).Value%2C%20Chr(10))%3CBR%20%2F%3ERange(%22A1%22).Resize(UBound(X)%20-%20LBound(X)%20%2B%201).Value%20%3D%20Application.Transpose(X)%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-184883%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-184883%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20this%20but%20want%20to%20split%20this%20data%20into%204%20cells%20VERTICALLY%20and%20not%20horizontally.%20Pls%20see%20attach%20for%20example%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183941%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183941%22%20slang%3D%22en-US%22%3E%3CP%3EDeepak%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Elets%20say%20your%20data%20starts%20from%20A2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPu%20this%20formula%26nbsp%3B%3DTRIM(MID(SUBSTITUTE(%24A2%2C%22%2F%22%2CREPT(%22%20%22%2C99))%2CCOLUMNS(%24A%242%3AA2)*99-98%2C99))%20in%20B2%20and%20drag%2Fcopy%20right%20and%20and%20down%20and%20it%20will%20do%20the%20job.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183823%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183823%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20formula%20is%20explained%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fsplit-text-with-delimiter%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fsplit-text-with-delimiter%3C%2FA%3E%3C%2FP%3E%0A%3CP%3ELooks%20like%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DTRIM(MID(SUBSTITUTE(%24B4%2C%22%2F%22%2CREPT(%22%20%22%2CLEN(%24B4)))%2C(L%243-1)*LEN(%24B4)%2B1%2CLEN(%24B4)))%3C%2FPRE%3E%0A%3CP%3Eand%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183802%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183802%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%3C%2FP%3E%3CP%3Eplease%20go%20through%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183792%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183792%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20a%20comment%20-%20if%20it%20is%20possible%20variable%20number%20of%20separators%20on%20rows%20number%20of%20columns%20is%20to%20be%20calculated%20additionally%2C%20something%20like%20this%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20ToText%20%3D%20Table.TransformColumnTypes(Source%2C%7B%7B%22Column1%22%2C%20type%20text%7D%7D)%2C%0A%20%20%20%20NumberOfDelimeters%20%3D%20Table.AddColumn(ToText%2C%20%22Parts%22%2C%20each%20List.Count(Text.Split(%5BColumn1%5D%2C%20%22%2F%22))-1)%2C%0A%20%20%20%20TotalColumns%20%3D%20List.Max(NumberOfDelimeters%5BParts%5D)%2B1%2C%0A%20%20%20%20ColumnsList%20%3D%20List.Transform(%7B1..TotalColumns%7D%2C%20each%20%22P%22%26amp%3BText.From(_))%2C%0A%20%20%20%20SplitColumns%20%3D%20Table.SplitColumn(ToText%2C%20%22Column1%22%2C%20Splitter.SplitTextByDelimiter(%22%2F%22%2C%20QuoteStyle.Csv)%2C%20ColumnsList)%0A%0Ain%0A%20%20%20%20SplitColumns%3C%2FPRE%3E%0A%3CP%3Eand%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183769%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183769%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Deepak%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20done%20by%20using%20Power%20Query%20also%20knows%20as%20(%3CSTRONG%3EGet%20%26amp%3B%20Transform%20Data%3C%2FSTRONG%3Ein%20Excel%202016)%2C%20and%20if%20you%20don't%20have%20Excel%202016%2C%20then%20you%20have%20to%20download%20and%20install%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D39379%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EPower%20Query%20Add-in%3C%2FA%3E.%3C%2FP%3E%3CP%3EPlease%20watch%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D0roS4OVfD9k%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Evideo%3C%2FA%3Eto%20figure%20out%20how%20to%20do%20this%20in%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20you%20can%20depend%20on%20this%20formula%20instead%3A%3C%2FP%3E%3CPRE%3E%3DMID(%24A1%2CIF(COLUMNS(%24A%241%3AA1)%26gt%3B1%2C(COLUMNS(%24A%241%3AA1)-1%26amp%3BCOLUMNS(%24A%241%3AA1)-1)%2B1%2CCOLUMNS(%24A%241%3AA1))%2C10)%3C%2FPRE%3E%3CP%3ECopy%20it%20in%20cell%20B1%20and%20then%20drag%20it%20to%20the%20right.%3C%2FP%3E%3CP%3EBut%20it%20works%20only%20if%20the%26nbsp%3Blength%20of%20the%20i%3CSPAN%3Envoice%20numbers%20%3C%2FSPAN%3Ein%20column%20A%20is%20always%2010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183634%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20data%20from%20one%20column%20without%20using%20text%20to%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183634%22%20slang%3D%22en-US%22%3EUse%20Data%2C%20From%20Table%20then%20click%20the%20Transform%20tab%20on%20the%20powerquery%20editor%20and%20click%20SPlit%20column.%20Fill%20in%20the%20details%20and%20you're%20good%20to%20go.%20If%20your%20source%20data%20changes%2C%20simply%20click%20refresh%20all%20on%20the%20data%20tab.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi,

 

I have some data(Invoice Nos) in a col A in a text format separated with"/", i want to split the same with using a formula (NOT TEXT TO COLUMN) coz if new data is added I'll need to go through the operation again.

I tried a combination of LEN, FIND but didn't succeed to get the desired result.

 

I have this

 

7000478203/7000525492/7000532700/7000536756/7000562365/7000571920

 

I want to do this

700047820370005254927000532700700053675670005623657000571920

 

Thanks

8 Replies
Highlighted
Use Data, From Table then click the Transform tab on the powerquery editor and click SPlit column. Fill in the details and you're good to go. If your source data changes, simply click refresh all on the data tab.
Highlighted

Hi Deepak,

 

This can be done by using Power Query also knows as (Get & Transform Data in Excel 2016), and if you don't have Excel 2016, then you have to download and install Power Query Add-in.

Please watch this video to figure out how to do this in Power Query.

 

Also, you can depend on this formula instead:

=MID($A1,IF(COLUMNS($A$1:A1)>1,(COLUMNS($A$1:A1)-1&COLUMNS($A$1:A1)-1)+1,COLUMNS($A$1:A1)),10)

Copy it in cell B1 and then drag it to the right.

But it works only if the length of the invoice numbers in column A is always 10.

 

Hope that helps

Haytham

Highlighted

As a comment - if it is possible variable number of separators on rows number of columns is to be calculated additionally, something like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToText = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    NumberOfDelimeters = Table.AddColumn(ToText, "Parts", each List.Count(Text.Split([Column1], "/"))-1),
    TotalColumns = List.Max(NumberOfDelimeters[Parts])+1,
    ColumnsList = List.Transform({1..TotalColumns}, each "P"&Text.From(_)),
    SplitColumns = Table.SplitColumn(ToText, "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), ColumnsList)

in
    SplitColumns

and attached

Highlighted

hi

please go through the attached file. 

Highlighted

Another formula is explained here https://exceljet.net/formula/split-text-with-delimiter

Looks like 

=TRIM(MID(SUBSTITUTE($B4,"/",REPT(" ",LEN($B4))),(L$3-1)*LEN($B4)+1,LEN($B4)))

and attached

Highlighted

Deepak,

 

lets say your data starts from A2

 

Pu this formula =TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMNS($A$2:A2)*99-98,99)) in B2 and drag/copy right and and down and it will do the job.

Highlighted

Thank you for this but want to split this data into 4 cells VERTICALLY and not horizontally. Pls see attach for example

Highlighted
then you use this code below.

lets say your data is in Cell A1

Sub Test()
Dim X As Variant
X = Split(Range("A1").Value, Chr(10))
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub