Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text to Columns (2000)

    Sorry about all the questions so close together. My manager just asked me something about using the Text to Columns feature.

    In one column she has a field she needs to Parse. The data isn't always the same for instance: Mr. Joe L Lewis - She needs to have the salutation part in one field, the first name in one field, the initial in one field and the last name in one field. The problem is there is not always a salutation and there is not always a middle initial. Is there a way to parse this field so that each part gets into the correct column? She has to have 4 columns even if some of the data is missing from the data. Your help is greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text to Columns (2000)

    I don't think that the Text to Columns feature can do this.

    See if the attached (zipped) workbook does what you want. It uses a custom function SearchReverse, and a series of intermediate results that I left visible in the workbook; you would probably hide them in a "production" workbook.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Columns (2000)

    That works except for the fact that the orignal field has extra spaces between the names. A big majority do. Is there anyway to remove the extra spaces in that column so that there is just one space between the names? The name sometimes has an extra space at the beginning and between the last name and the initial or if no initial the first name.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text to Columns (2000)

    You can use TRIM to trim the names first. Then work with the TRIMmed names.

    TRIM removes leading and trailing spaces and also removes all but one internal space.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Columns (2000)

    Thanks - than saved me a lot of editing. I didn't realize that TRIM removed all extra spaces.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •