1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Open Office/Excel - Macro Help

Discussion in 'Business Applications' started by JudeWright, Apr 19, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. JudeWright

    JudeWright Thread Starter

    Joined:
    Apr 19, 2009
    Messages:
    2
    Hey, I was just wondering if anyone knew how to create a macro that takes each letter from a string in one cell, and puts each one into its own cell. For example, if I pasted this paragraph into cell A1 then click my macro, this happens (edit: doesn't matter what case the letters come out in; caps or as they were written):


    [​IMG]

    EDIT: A macro to do the opposite would be useful too, i.e. take all the information from the seperate cells and put it into one string. Thanks.

    EDIT2: The formula CONCATENATE(a1;b1;c1) etc seems to work for the opposite, except for the fact that i'm working with strings 1000 characters long, and CONCATENATE(a1:z100) or whatever doesn't work, it just returns the letter in A1. Any ideas?
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Look at Text to Columns on the Data menu. You can use a space character as the delimiter and it will put each word into it's own cell. There should be no data to the right of the cell you wish to perform this on.

    For getting all of the information into one cell, you should download/install the morefunc.xll add-in by Laurent Longre (a google search will lead you there), and then you can use the MCONCAT() function. This would be an example...

    =MCONCAT(A1:G1," ")

    ...where A1:G1 is the range of cells you want to concatenate, and you want to delimit it by a space between each cells value.

    HTH
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "a macro that takes each letter from a string in one cell, and puts each one into its own cell."

    No macro required. For Excel, select A2:J2, click in the formula bar, paste this:

    =MID($A$1,((ROW()-2)*10)+COLUMN(),1)

    and press CTRL+Enter.

    If you want it to span less/more columns, just adjust *10. The functions involved are also available in (OpenOffice) Calc, just use semicolons instead of commas to separate the arguments.

    HTH :)
     
  4. JudeWright

    JudeWright Thread Starter

    Joined:
    Apr 19, 2009
    Messages:
    2
    Thank you very much! Your 2 replies have solved both my problems perfectly, it's exactly what I needed :)

    Edit: Oh, just one thing, will the morefunc.xll work with Open Office too? I'm at school at the moment but as soon as I get home I'll try it.
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    From the download page:

    "These functions are compatible with Excel 95 to 2007. They can't be used with other spreadsheets."

    :(
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/820100

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice