+ Reply to Thread
Results 1 to 9 of 9

Distributing Text within cell

Hybrid View

  1. #1
    Registered User
    Join Date
    02-24-2012
    Location
    Tasmania
    MS-Off Ver
    Excel 2013
    Posts
    5

    Exclamation Distributing Text within cell

    Hello, I have a listing of data which I need to have distributed in a uniform manner within each cell eg:
    MOTOR Ge 6244D
    MOTOR Ge 6244D
    MOTOR KLELEC 5368 1460RPM 5KW
    MOTOR AGE AK948 1500RPM 10hp
    MOTOR KLELEC 4876 1500RPM 12.5hp

    What is required is something like this;
    MOTOR Ge 6244D
    MOTOR Ge 6244D
    MOTOR Klelec 5368 1460RPM 5KW
    MOTOR Age AK948 1500RPM 10HP
    MOTOR Klelec 4876 1500RPM 12.5HP

    Within each cell the data is distributed at what ever number of chars determined.
    Motor - 5 Chars
    GE - 9 Chars etc...

    Thanks
    Jamie

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

    Re: Distributing Text within cell

    Hi JMButton,

    Welcome to the forum.

    It would be more helpful and you will likely get more response if you attach a small sample of an Excel file (desensitized of course) that clearly exhibits the BEFORE (what you are starting with) and an AFTER section exhibiting desired output. 20 rows or so is usually sufficient. It makes clear what words often don't.

    If you are not familiar with how to do this click the 'Go Advanced' button (lower right) of the reply edit window. Scroll down and find and click the blue text 'Manage Attachments'. Follow the instructions in the pop-up window. An icon with your workbook title will appear when you click the 'Reply' button to your post.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,708

    Re: Distributing Text within cell

    In the meantime does this resemble what you want?

    With this formula in B1 and filled down.
    Formula: copy to clipboard
    =SUBSTITUTE(UPPER(A1),UPPER(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256,256))),PROPER(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256,256))))
    If not we'll need a workbook.



    A
    B
    1
    MOTOR Ge 6244D
    MOTOR Ge 6244D
    2
    MOTOR Ge 6244D
    MOTOR Ge 6244D
    3
    MOTOR KLELEC 5368 1460RPM 5KW
    MOTOR Klelec 5368 1460RPM 5KW
    4
    MOTOR AGE AK948 1500RPM 10hp
    MOTOR Age AK948 1500RPM 10HP
    5
    MOTOR KLELEC 4876 1500RPM 12.5hp
    MOTOR Klelec 4876 1500RPM 12.5HP

  4. #4
    Registered User
    Join Date
    02-24-2012
    Location
    Tasmania
    MS-Off Ver
    Excel 2013
    Posts
    5

    Post Re: Distributing Text within cell

    FlameRetired;

    As requested I have attached a sample file of what is required.
    As I am uploading this data( 250,000 lines ) of all different equipment types into a MMS this is the description of the piece of equipment in a more uniform format for identification.

    Jamie
    Attached Files Attached Files

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,708

    Re: Distributing Text within cell

    The upload and the data in post #1 show mixed case inputs / outputs with no consistent patterns that I can discern. Please enlighten me.

  6. #6
    Registered User
    Join Date
    02-24-2012
    Location
    Tasmania
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Distributing Text within cell

    Ok, the first word in then string is MOTOR + space - 6 Chars, the next set of characters is a Manufacture which is a Pneumonic of 8 chars and so on. I need each criteria to be exactly the number of defined chars for the data.
    Motor - 9 char
    Mfr - 8 char etc. etc.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,708

    Re: Distributing Text within cell

    The

    "This is how I would like it represented." output in the upload does not reflect that pattern after the 2nd row.

    I am also a big 'foggy' on the etc. etc. etc. part.

    We're going to need more detail.

  8. #8
    Registered User
    Join Date
    02-24-2012
    Location
    Tasmania
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Distributing Text within cell

    I am at a bit of a loss, I can't explain it more simply, if the string is broken up into separate numbers of characters determined
    the first word 'MOTOR' is 5 characters, the second set of information is a Pneumonic of a manufacturer which is 8 characters.
    The string which I am wanting to output will have - MOTOR CROMPARK XXXXXXXX XXXXXXXX XXXXXXXX XXXXXXXX.
    This will ensure that all data is in a uniform format, weather the length of the data to be output is 3 characters or 5 characters the format will be the same.

  9. #9
    Registered User
    Join Date
    02-24-2012
    Location
    Tasmania
    MS-Off Ver
    Excel 2013
    Posts
    5

    Unhappy Re: Distributing Text within cell

    With a bit more investigating I have refined my request, I am requesting a Tabular output from a text cell where the outputs are aligned.

    JB

+ 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. Help on Distributing values
    By ganjakid in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-22-2010, 04:26 AM
  2. Distributing Cost
    By Mayala03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2009, 02:44 AM
  3. Distributing Data
    By Milnet in forum Excel General
    Replies: 6
    Last Post: 08-10-2009, 02:55 AM
  4. Replies: 2
    Last Post: 04-22-2009, 04:55 PM
  5. [SOLVED] Distributing a Cell Value
    By MLCole in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-07-2006, 01:30 PM
  6. [SOLVED] Distributing a UDF
    By David in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2005, 08:00 AM
  7. [SOLVED] Distributing Changes in Workbook
    By JohnGoogle@hotmail.co.uk in forum Excel General
    Replies: 3
    Last Post: 10-14-2005, 09:05 PM
  8. [SOLVED] Distributing an add in
    By Daniel McCollick in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2005, 03:05 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