+ Reply to Thread
Results 1 to 9 of 9

Splitting data from cells (examples included)

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    Cheshire, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Splitting data from cells (examples included)

    Hello

    Firstly, apologies if I have posted in the wrong sub-forum; I am relatively new to Excel and more so the forums.

    My friend has asked me to assist him in sorting his data/spreadsheets but I am somewhat stuck at this particular moment in time.

    I am using Excel 2010.

    I have attached two files; the first file entitled 'Template for data that requires sorting.xlsx', and then what I hope will be the result, entitled 'Template for data result.xlsx'.

    I would very much appreciate any help on this matter, as I have an awful lot of data that can hopefully be sorted in the aforementioned format.

    If you require any further information, please ask.

    Many thanks,
    Mark
    Last edited by Memort; 04-10-2013 at 02:25 PM.

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Splitting data from cells (examples included)

    Is all your <ClientAddress> without any delimiter like "/" at <tel1>

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Splitting data from cells (examples included)

    Without delimiters in "ClientAddress", I'm afraid you'll have to do this manually.

    How can excel tell where to split the string?
    e.g. you need this, split at the commas.
    1 Green Park Lane, Steeton, Keighley, West Yorkshire, BD24 7RE
    How can we tell that this is not correct?
    1 Green Park, Lane Steeton, Keighley West, Yorkshire, BD24 7RE
    Extra spaces don't help either!
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    04-10-2013
    Location
    Cheshire, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Splitting data from cells (examples included)

    Thank you both for your responses.

    wenqq3, the problem I have is, there are approximately 260,000 records and whilst some of the <ClientAddress> entries do have delimeters, many do not. The same is applicable for the telephone numbers; some do and some do not.

    Marcol, I completely understand what you are saying regarding telling Excel where to split the data if there are no delimeters.

    I informed my friend - who is somewhat of a technophobe, I must stress - of your responses and he replied: "Can it not be converted by a programme via Access or put into notepad and reconfigured by CSV?". He is currently away on business so I am only receiving intermittent correspondence, but what I have garnered from his response is, can the 260,000 records be exported from Excel into another program (such as Access or Notepad) and then perhaps amended into a suitable format and then imported back to Excel? I hope that is clear.

    Thanks,
    Mark

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Splitting data from cells (examples included)

    "Can it not be converted by a programme via Access or put into notepad and reconfigured by CSV?"
    I'm afraid not, unles your "technophobic" friend knows of a programme that can permutate all the possible options, then can decide on the one he/she requires.

  6. #6
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Splitting data from cells (examples included)

    -Suggest the telephone numbers still can split by space.
    -For those <client address>, you may found some criteria. Like after "road"/"rd"/"lane"/"ave"/"avenue"/"park"and many others, you can add delimited after those words, simply by replace.
    -If those 260,000 records from same country,try google their city name, state name, and add delimited after those words (replace"city name" + ",").

    Hope this can help you abit.

  7. #7
    Registered User
    Join Date
    04-10-2013
    Location
    Cheshire, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Splitting data from cells (examples included)

    Okay, thank you for your time, Marcol.

    wenqq3, thanks. With regards to the telephone numbers; how would I split them by space, please? I have included examples (this time I have removed the "/" that was shown in my opening my post example).

  8. #8
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Splitting data from cells (examples included)

    - Select the column < telephone >
    - Text to Columns
    - Choose your delimited "SPACE"
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Splitting data from cells (examples included)

    Text to Columns will only work if you add extra column after the column you need to split, you can use the destination option, but this will not allow you to choose an address on a different sheet.

    With your datasheets clear all formatting first, this will make life easier.
    Your destination sheet is formatted for text, if this isn't cleared any formula you use will show as text and not calculate.

    See the formula in A2 This can be dragged across and down as required, I have added an extra column, Column D, to allow for the possiblity that the original name has more than 3 parts.

    The formula in E2, should remove the Post Code from the "ClientAddress", assuming it is always in 2 parts.
    Then in I2 we can extract the Post Code.

    J2 Across and Down works in a similar way to A2.

    You can then copy the lot and paste special > values for further editing.

    This is not a bullet proof solution, just something to show you some ways to manipulate data strings, and perhaps also reduce some of the manual editing you will have to do.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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