Need Excel help too: Find/Replace by Column...

Discussion in 'OT Technology' started by z284pwr, Sep 9, 2008.

  1. z284pwr

    z284pwr OT Supporter

    Joined:
    Jun 16, 2002
    Messages:
    8,004
    Likes Received:
    36
    Location:
    Fruit Heights, Utah
    I'm looking to see if these is even possible....I have a list, ~3,000 entires, formated like this.

    Code:
    Customer First	Customer Last	Phone		Address						City		State	Zip
     Jayme E.	Barrett				Jayme E. Barrett PO Box 434 Belchertown		Belchertown	MA	01007
     Aubert		Giles		413-596-8744	Aubert Giles 60 Oakland St. Wilbarham		Wilbarham	MA	01095
     Cary		Decenzo		413-567-6403	Cary Decenzo 378 Inverness Ln. Longmeadow	Longmeadow	MA	01106
     Maryann	Steel		413-783-7138	Maryann Steel 1100 Parker St. Springfield	Springfield	MA	01129
    
    
    I exported this list from Quickbooks and it merged ALL the information from the Bill To entry into 1 column so now I'm trying to get the addresses separated as I have everything except the names and the cities separated from the column.

    Is it possible to do a Find/Replace by basically finding the text of Column A (First Name) Row 1 in Column 4 (Address) and replacing it with a space basically to remove it and repeating this for the Last Name and City columns so it has just the address remaining. Separating text to columns by space takes forever and would be faster to basically just type the addresses individually :hs:
     
  2. trouphaz

    trouphaz New Member

    Joined:
    Sep 22, 2003
    Messages:
    2,666
    Likes Received:
    0
    if it is all formatted like that, it wouldn't be too hard to do... on a UNIX machine with some scripting knowledge. :)

    i use excel and scripts a lot to format text into CSV files to get it laid out how i want.


    by the way, i hope you didn't just throw a bunch of your client's names, addresses and phone numbers up on OT. that's kind of fucked up.
     
  3. Bruticus

    Bruticus half dead OT Supporter

    Joined:
    Apr 10, 2004
    Messages:
    4,608
    Likes Received:
    0
    Location:
    Melbourne
    Should be possible, I recently did it on Lotus 1-2-3 (didn't have Excel on that work machine) without any problems. You could also just write a quick and simple perl script to help you if Excel is being annoying.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Do a search on the substitute command in excel - it does what you want.
     

Share This Page