+ Reply to Thread
Results 1 to 5 of 5

A macro for: a) splitting the contents of one cell into two; b) reformatting the 2nd cell.

  1. #1
    Registered User
    Join Date
    01-02-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2003
    Posts
    63

    A macro for: a) splitting the contents of one cell into two; b) reformatting the 2nd cell.

    Hi all,

    I have a spreadsheet that is several thousand rows long and the columns run from A:K. Column K has the data I'd like to split.

    What I'd like to be able to do is:

    1. Split Columns K into two columns, so that it becomes columns K & L;
    2. Change column L to a bullet point format, so that when you click on the cell it appears as a bullet point list as opposed to all the data being mushed up on the one line.

    Here is a screenshot: http://i.imgur.com/0q7srwP.png

    Attached is also the spreadsheet with a current layout and the desired layout.

    At present, my laborious solution involves the following:

    - find and replace;
    - text to columns;
    - concatenate;
    - transpose;
    - cut and paste to notepad;
    - copy and paste to excel.

    Any help would be greatly appreciated
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: A macro for: a) splitting the contents of one cell into two; b) reformatting the 2nd c

    Hi

    Your idea is on the right tracks but just needs adjustment.

    Not sure why you need a macro but here is how I would do it.

    Select col K - Find and select - replace - • - with ? (yes, a question mark, just that) - replace all - Ok - close

    Select col K - Text to columns - delimited - next - other - insert the ? - next - now select each column one by one and format as text or skip i'e' don't import - finish

    Col L now needs reformatting to move text left

    Don't see the point in having bullet points. That can only frustrate you in the future if you need to sort by that column for example.
    If I helped, please don't forget to add to my reputation. (click on the star below the post)

    If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.

    Failure is not falling down but refusing to get up.

  3. #3
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: A macro for: a) splitting the contents of one cell into two; b) reformatting the 2nd c

    Just had a thought. Were you intending all the data on each row to be listed when selecting a cell? You didn't say or demonstrate that in your example but it seems pretty likely from reading again.

    You could leave all cols as general even importing the blank when on T to C. Best to do a drop down list Using L1 select data validation. Select all to the right as source. Now that would give you jus one row. As you say, it would need code to do all teh rest. Beyond me I'm afraid but if I'm right we can put it to the experts.

  4. #4
    Registered User
    Join Date
    01-02-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: A macro for: a) splitting the contents of one cell into two; b) reformatting the 2nd c

    Quote Originally Posted by Russell Dawson View Post
    Hi

    Your idea is on the right tracks but just needs adjustment.

    Not sure why you need a macro but here is how I would do it.

    Select col K - Find and select - replace - • - with ? (yes, a question mark, just that) - replace all - Ok - close

    Select col K - Text to columns - delimited - next - other - insert the ? - next - now select each column one by one and format as text or skip i'e' don't import - finish

    Col L now needs reformatting to move text left

    Don't see the point in having bullet points. That can only frustrate you in the future if you need to sort by that column for example.
    .

    hmmm, not quite what i was looking for

    i appreciate your help

    your proposed solution doesnt seem all that diff to what i am doing now. I was actually hoping to eliminate a few steps and somehow put those steps into a macro

    thank you for your help all the same

    .

  5. #5
    Registered User
    Join Date
    01-02-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: A macro for: a) splitting the contents of one cell into two; b) reformatting the 2nd c

    Quote Originally Posted by Russell Dawson View Post
    Just had a thought. Were you intending all the data on each row to be listed when selecting a cell? You didn't say or demonstrate that in your example but it seems pretty likely from reading again.

    You could leave all cols as general even importing the blank when on T to C. Best to do a drop down list Using L1 select data validation. Select all to the right as source. Now that would give you jus one row. As you say, it would need code to do all teh rest. Beyond me I'm afraid but if I'm right we can put it to the experts.
    thanks again Russell, but im not after a dropdown list

    I provided a screenshot of what im after as well as a spreadsheet, i tried to include as much detail as possible. Did you manage to take a look at that? (Not sure if it attached correctly). As such, i dont really know how else to describe my desired outcome.

    here is another screenshot (not too dissimilar to the one i attached previously)

    I hope its a little clearer
    Attached Images Attached Images

+ 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. Excel 2007 : Help with splitting contents of a cell
    By BigGPL in forum Excel General
    Replies: 4
    Last Post: 01-22-2012, 07:21 AM
  2. Splitting cell contents
    By Jeshen in forum Excel General
    Replies: 6
    Last Post: 12-20-2011, 12:39 PM
  3. Splitting the contents of a cell
    By sheargraphix in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-14-2011, 07:11 AM
  4. Splitting cell contents
    By novodisc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-12-2010, 04:49 PM
  5. Splitting the contents of a cell?
    By Dan B in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-08-2005, 04:40 PM

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