Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wildcard and replace (xp)

    Hello,

    I have a list of 11000 names. Most of the names are formatted thus: Gene.Wood However some are formatted thusly: .Admin

    I want to be able to find all of those names that have a character followed by a period and replace with a comma and a space (e.g. Gene, Wood). The problem is removing the period in front of a name that has no character in front of the period, such as .Admin

    When I use the wildcard to find ?. and replace with , those names with the character in front of the period gets truncated.

    Any ideas?

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wildcard and replace (xp)

    Gene,
    How about searching for . (period) and replace with , (comma space). Then sort the list and just strip the comma using a formula?
    =mid(A1,2,100) takes the next 100 characters, starting with the second character, of the contents of Cell A1

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wildcard and replace (xp)

    Chuck,

    Thanks, you gave me the idea. The list was sorted but I didn't put 2x2 together (until just now) that I could just select those records that had the leading period (which were the top records) and do a replace period with nothing.

    Man, I feel really stupid. Sometimes you just need to talk it out with somebody to get out of your own head, ya know?? THanks!!

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wildcard and replace (xp)

    Gene,

    Sometimes all it takes is asking the question. I am glad you were able to solve your problem.
    I have been in your position far too many times, and always appreciate the Lounge.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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