Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cut string of text (excel pro 00)

    Anyone know a formula that can cut the names below at every " " and remove the "," replacing it with "" so basically the names are starting at b1 down

    in e1 Joe
    in f1 Thorton
    in g1 SJ


    Joe Thornton, SJ
    Jaromir Jagr, NYR
    Ilya Kovalchuk, ATL
    Daniel Alfredsson, OTW
    Dany Heatley, OTW
    Marc Savard, ATL
    Eric Staal, CAR
    Marian Hossa, ATL
    Peter Forsberg, PHI
    Simon Gagne, PHI
    Pavel Datsyuk, DET
    Alex Tanguay, CO
    Sidney Crosby, PIT
    Markus Naslund, VAN
    Vaclav Prospal, TB
    Ladislav Nagy, PHX
    Patrick Marleau, SJ
    Jason Spezza, OTW
    Pavol Demitra, LA
    Brian Gionta, NJ
    Marco Sturm, BOS
    Henrik Zetterberg, DET

  2. #2
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: cut string of text (excel pro 00)

    put this into e1
    =SUBSTITUTE(LEFT(B1,FIND(" ",B1,1))," ","",1)
    put this formula in f1
    =SUBSTITUTE(MID(REPLACE(B1,FIND(",",B1,1),15,""),F IND(" ",REPLACE(B1,FIND(",",B1,1),15,""),1),15)," ","",1)

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cut string of text (excel pro 00)

    For the first name:
    =LEFT(A1,FIND(" ",A1)-1)

    Last name:
    =MID(A1,FIND(" ",A1)+1,FIND(",",A1)-1-FIND(" ",A1))

    Team:
    =RIGHT(A1,LEN(A1)-FIND(", ",A1)-1)

    Alternately:

    In column "G" use:
    =SUBTITUTE(A1,",","")
    Copy the formula down as far as you need. Next copy the entire range and do a Paste Special... Values. With the range still highlighted, go to Data Text to Columns... Choose delimited and the choose "Space" as you delimiter. Click on finish.

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

    Re: cut string of text (excel pro 00)

    1- Make sure there are two empty columns next to the column of names.

    2- Select the column of names.

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

    4- In the first dialog box, place a tick mark next to Delimited and click Next.

    5- In the next dialog box put check marks next to "Space" and "Comma". Click Finish.

    You should have three columns separated as you asked.
    Legare Coleman

Posting Permissions

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