+ Reply to Thread
Results 1 to 12 of 12

Data Separation

  1. #1
    Registered User
    Join Date
    10-10-2017
    Location
    London
    MS-Off Ver
    15.33 Office 365
    Posts
    4

    Post Data Separation

    Hi I have a dataset in text such as:

    Apples have been sold by Ben to Joe for 15£
    Peach have been sold by Patrick to Jessica for 154£
    Lemons have been sold by Mark to Hanna for 1544£

    I was wondering if it would be posible to separete the information in columns such as that:

    In Column A; have What has been sold
    In Column B: Who sold it
    In Coumb C: To whom it was sold
    In columb D: How much was sold

    I've tried to do the Data>Text to Columns and use spaces to break up the columns but it did not work as it grouped the data on the wrong way.


    Any Help is more than welcome thanks!

    Oh and is my first post so Hi to everyone and glad to be part of this forum!

    Cheers!
    Delac147

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Data Separation

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Data Separation

    or use PowerQuery (Get&Transform)
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Data Separation

    You can use variants of this formula:

    =TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",125)),125*COLUMNS($A:A),125))

    see sheet.
    Attached Files Attached Files
    Glenn



  5. #5
    Registered User
    Join Date
    10-10-2017
    Location
    London
    MS-Off Ver
    15.33 Office 365
    Posts
    4

    Re: Data Separation

    Here is a Workbook Sample, so you can get a better idea of what I need, I've also attached the real data I want to break down.


    Again, any help is appreciated!


    And many thanks to all that replied!


    Cheers,
    delac147
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Data Separation

    Now we have some realistic data that are FAR more complicated than your original example....

    in B2, copied across and down:

    =TRIM(MID(SUBSTITUTE("¦"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"*es traspasado de*","¦"),"*a*","¦"),"*por*","¦"),"¦",REPT(" ",125)),125*COLUMNS($A:A),125))
    Attached Files Attached Files

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Data Separation

    Your original data is mixed like example ?
    Attached Files Attached Files

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Data Separation

    Maybe like this one:
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-10-2017
    Location
    London
    MS-Off Ver
    15.33 Office 365
    Posts
    4

    Re: Data Separation

    Great! That works wonderfully! Much appreciated!

    Cheers!

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Data Separation

    ?????
    which one

  11. #11
    Registered User
    Join Date
    10-10-2017
    Location
    London
    MS-Off Ver
    15.33 Office 365
    Posts
    4

    Re: Data Separation

    Thanks, Everyone!

    Here @GlennKennedy solved it for me!

    Thanks for all the help!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Data Separation

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. separation of data
    By alireza123456 in forum Excel General
    Replies: 11
    Last Post: 08-06-2016, 07:42 AM
  2. Replies: 3
    Last Post: 05-18-2016, 12:03 PM
  3. [SOLVED] Data Separation
    By ssakthish in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2013, 02:34 AM
  4. Data separation from table
    By Elen_Ch in forum Access Tables & Databases
    Replies: 2
    Last Post: 09-11-2012, 07:02 AM
  5. data separation
    By oxdude in forum Excel General
    Replies: 2
    Last Post: 03-01-2009, 04:52 PM
  6. Data Separation from one cell to two
    By Bundok@mac.com in forum Excel General
    Replies: 4
    Last Post: 02-14-2009, 11:14 PM
  7. Auto data separation
    By BRISBANEBOB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-15-2009, 04:33 AM

Tags for this Thread

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