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.

alphanumeric strings

Discussion in 'Business Applications' started by reporter1, Sep 19, 2008.

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

    reporter1 Thread Starter

    Joined:
    Mar 6, 2008
    Messages:
    43
    Hi Excel Pros,

    I'm no excel wizard and I'm having difficulty separating strings of text and numbers from one cell into separate columns.

    Here's an example:

    MONSEF,BERNADETTE03/25/910930DMRAAM10-t/f1000150012001730

    I'd like the information separated as follows:

    MONSEF,BERNADETTE 03/25/91 0930 DMRA AM10-t/f 1000 1500 1200 1730

    Each row has different length names, dates, times etc...

    Is this even possible to accomplish with a formula?

    Dizzy,
    reporter1
     
  2. -Fabez-

    -Fabez-

    Joined:
    Jul 28, 2008
    Messages:
    1,899
    So you would like to split a string into smaller strings, by finding a series of charactars and splitting at that point ? If this is not what you meant could you elaborate please :D
     
  3. reporter1

    reporter1 Thread Starter

    Joined:
    Mar 6, 2008
    Messages:
    43
    Fabez,

    Yes, but with some strings being different lengths, I can't do delimited text to columns. Here's another example:

    LOPRIORE,JOHN09/27/941200PM10-fss1000173014451945

    Should read:

    LOPRIORE,JOHN 09/27/94 1200 PM10-FSS 1000 1730 1445 1945

    The last four sets of numbers are all made up of 4 characters (1000, 1730, 1445, 1945)

    Is there help for me?
     
  4. -Fabez-

    -Fabez-

    Joined:
    Jul 28, 2008
    Messages:
    1,899
    Where is the data generated as it would be easier to include a seperator string when it is made. If you have no control over its making then it would be best to use wild cards and generic strings to search and split the main string.
     
  5. reporter1

    reporter1 Thread Starter

    Joined:
    Mar 6, 2008
    Messages:
    43
    Fabez,
    The PDF file is created by someone else so I can't change the original format. I'm not very familiar with wild cards and generic strings. How would I get each string to separate at points where numbers meet numbers if the number of characters is different? Is the wild card used for numbers that are all the same in length?

    Thanks,
    reporter1
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Are some of those numbers always the same length?
    Like the last 4 for instance?
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This might be possible using VBA.
     
  8. reporter1

    reporter1 Thread Starter

    Joined:
    Mar 6, 2008
    Messages:
    43
    Yes, the last four sets of numbers are all four characters.
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Would you want to seperate the Second name from the comma with a space?
     
  10. reporter1

    reporter1 Thread Starter

    Joined:
    Mar 6, 2008
    Messages:
    43
    I don't know how to do VBA. Wish I did!
     
  11. reporter1

    reporter1 Thread Starter

    Joined:
    Mar 6, 2008
    Messages:
    43
    No need to add spacing for the names
     
  12. -Fabez-

    -Fabez-

    Joined:
    Jul 28, 2008
    Messages:
    1,899
    Okay then. A wild card is something that can represent anything, like * does in Actionscript, a generic string is a general string like, dd / mm / yy.

    LOPRIORE,JOHN09/27/941200PM10-fss1000173014451945

    LOPRIORE,JOHN 09/27/94 1200 PM10-FSS 1000 1730 1445 1945

    In order to achieve the seperation on this string you would have to follow several steps.

    I could either tell you how to do this in Excell or another language if you want :D
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If it can wait until tomorrow and none of the Excel Guys come along I can create you some VBA that should do it.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If Fabez doesn't do it for you let me know.
     
  15. reporter1

    reporter1 Thread Starter

    Joined:
    Mar 6, 2008
    Messages:
    43
    I think I'd prefer excel so I can learn something from it in case something similar occurs again in the future. It's no emergency - it can wait until Monday. I'll be leaving here (work) in 20 minutes.

    Thanks -
    reporter 1
     
  16. 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!

Thread Status:
Not open for further replies.

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

  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