Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Is it possible to separate data in a cell into two or more cells?

    For instance the data in a cell is name, street address, city and state and we want to separate that one cell into 4 cells. I have included a small file as an example.

    Thank you,
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached workbook. I used some auxiliary formulas:

    In G9, the position of the first space: =FIND(" ",A9)
    In H9, the position of the slash after the first space: =FIND("/",A9,G9+1)
    In I9: the position of the comma after the slash: =FIND(",",A9,H9+2)

    These are used in the other formulas:

    To get the name in C9: =LEFT(A9,G9-1)
    To get the address in D9: =MID(A9,G9+1,H9-G9-1)
    To get the city in E9: =TRIM(MID(A9,H9+1,I9-H9-1))
    To get the state in F9: =TRIM(MID(A9,I9+1,100))

    (I used TRIM so that it doesn't matter whether there is a space after the slash or comma)
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    how would the formula change if there was a"," instead of a "/" after "St."

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Replace the slash / with a comma , in the formula

    =FIND("/",A9,G9+1)

Posting Permissions

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