Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strings and concatenation (Excel 97-SR2)

    My staff is receiving mailing lists, which were data lists exported to Excel.
    There is a slight hitch that my staff can't work with.
    The field [Name] is populated by a reverse concatenated value of Last Name, "," First Name.
    Is there a simple replace script or series of wild cards which Excel can use to replace this order?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Strings and concatenation (Excel 97-SR2)

    Chip Pearson's name formulas has the info that you need. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strings and concatenation (Excel 97-SR2)

    If your data is as simple as your description, then the following should work.

    1- Make sure there is an empty column nest to the column of names.

    2- Select the column of names.

    3- Select "Text to columns..." from the Data menu.

    4- In the dialog box make sure "Delimited" is selected and click on the Next button.

    5- In the dialog box, in the delimiters section, select Comma and unselect everything else.

    6- Click on the Finish button.

    If your names are more complex, you may need Chip Pearson's solution that SammyB recommended to you.
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strings and concatenation (Excel 97-SR2)

    Well, that parser was certainly a handy function to include.
    Now...how about concatenating columns?
    If they were then moved to the correct order can I concatenate them to one?
    Could I also reverse the order of concatenation (e.g. B& A)?
    (i.e. First& Last) ?

    I was thinking that I could use the comma as the inStr char
    Declare everything to the left as str1 and everything to the right as str2
    then use a replace function and /2 & /1 kind of thing.
    Sort of like the best of Word but used in Excel.
    Is this feasible?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Strings and concatenation (Excel 97-SR2)

    If you've got last name in A1 and First name in B1, you could use a formula in C1 of:
    =B1&" "&A1
    to rejoin them.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strings and concatenation (Excel 97-SR2)

    Purrrrrrfect.
    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Thanx

Posting Permissions

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