Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006

    Unanswered: Separating Data in one cell to multiple cells

    I have an excel file that contains 4 different data items in a single column separated by a <space>. I need to write a subroutine that separates the string of characters in the cell based on the positions of the <spaces>.
    Example string "France Adam Down Holiday"
    Dont think there is a combination of spreadsheet excel functions that will do this. Therefore will have to write some visual basic code.
    Need some help here if possible in a little bit of code.
    Im thinking a combination of "Instr" and "Left" VB functions will do this for me. Is there a simpler way of splitting the string using other string manipulation functions?

    Any help is much appreciated.

  2. #2
    Join Date
    Mar 2006
    Why not just use Data>Text to columns...?

    If you really need code then you could use the macro recorder when you do that manually.

  3. #3
    Join Date
    Oct 2003
    I agree with Norie on this. However, if you want a couple of formulas, then for the

    first word of the cell (A28) use:

    =IF(ISERR(LEFT(A28,FIND(" ",A28)-1)),A28,LEFT(A28,FIND(" ",A28)-1))

    Last word of the cell (A28) use:

    =IF(LEN(A28)-LEN(SUBSTITUTE(A28," ",""))=0,A28,RIGHT(A28,LEN(A28)-FIND("*",SUBSTITUTE(A28," ","*",LEN(A28)-LEN(SUBSTITUTE(A28," ",""))))))

    To get the third word, it is two steps:

    This gives you the first three words (put this in cell F28):

    =LEFT(A28,LEN(A28)-LEN(IF(LEN(A28)-LEN(SUBSTITUTE(A28," ",""))=0,A28,RIGHT(A28,LEN(A28)-FIND("*",SUBSTITUTE(A28," ","*",LEN(A28)-LEN(SUBSTITUTE(A28," ","")))))))-1)

    Then this gives you the third word, by building on the above formula (you can't combine, because it goes beyond 1024 characters) (put in cell G28)

    =IF(LEN(F28)-LEN(SUBSTITUTE(F28," ",""))=0,F28,RIGHT(F28,LEN(F28)-FIND("*",SUBSTITUTE(F28," ","*",LEN(F28)-LEN(SUBSTITUTE(F28," ",""))))))

    Given this, you should be able to extract the second word.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Feb 2006

    Thanks for the help

    Thanks for the help. Was just looking for the easy way to do it and the text to columns worked for me .

Posting Permissions

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