+ Reply to Thread
Results 1 to 9 of 9

Formula required to split uneven data into different columns

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Formula required to split uneven data into different columns

    Hi All

    I have a database to split data into three different columns in the second column B I managed with a formula, but stuck in column C and D because as data are not uniform.

    Annexed sample

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula required to split uneven data into different columns

    See if the attached does what you want.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Formula required to split uneven data into different columns

    hi

    Formula for code
    =LEFT(A2,FIND(" ",A2)-1)

    Formula for "origin"
    =MID(A2,SEARCH(" ?? ",A2)+1,2)

    Formula for "description"
    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,E2,""),"Total","")," "&G2&" ",""))
    Appreciate the help? CLICK *

  4. #4
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Formula required to split uneven data into different columns

    Hi
    The results are ok, but there are some lines that did not give the expected results.

    eg( sample row 3) 22042190 WINES ZA (SADC R539722) Total should read 22042190 WINES (SADC R539722) ZA

    ROW 3,4,8,10,11 should read same,this is a sample, the database have minimum 500 rows like this uneven .Very tedious.

    Is it possible.

    Thanks

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Formula required to split uneven data into different columns

    Hi

    Great many thanks

    Works marvellously

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula required to split uneven data into different columns

    Quote Originally Posted by JEAN1972 View Post
    Hi
    The results are ok, but there are some lines that did not give the expected results.

    eg( sample row 3) 22042190 WINES ZA (SADC R539722) Total should read 22042190 WINES (SADC R539722) ZA

    ROW 3,4,8,10,11 should read same,this is a sample, the database have minimum 500 rows like this uneven .Very tedious.

    Is it possible.

    Thanks
    Yes. I misinterpreted. If you go with AZ-XL's solution it does that. Mine stripped out the parentheticals.

  7. #7
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Formula required to split uneven data into different columns

    thanks for your help

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula required to split uneven data into different columns

    You're welcome. Glad you found a solution.

  9. #9
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Formula required to split uneven data into different columns

    Hi
    You are welcome and thanx for reps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Fucntion Required for Split data
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2013, 11:10 AM
  2. Split uneven data from a single columns to multiple columns
    By pfoz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-13-2013, 08:24 AM
  3. Sort and Match Uneven Columns of data
    By MustardMan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2012, 01:43 PM
  4. Combining Text split across uneven number of rows
    By DominicJ in forum Excel General
    Replies: 4
    Last Post: 07-26-2012, 09:31 AM
  5. Replies: 6
    Last Post: 07-05-2011, 07:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1