Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Complex(?) formulae needed...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi folks,

    I've been looking through as I'm sure the answer to this question has already been covered on the board in many different guises but I can't find quite what I want.

    Basically, say in Column A I have a list of cars/manufacturers that people have entered.
    I've tidied up the entry a bit so that all entries are now of the format:

    ManufacturerModelAdditionalInfo
    i.e.
    BMW 320
    BMW 316 Compact
    Honda Civic
    Honda Civic 1.5L

    What I then need in Columns B and C and D are the split out manufacturers in B, models in C, models + and other info in D.
    I know I could do this with data|text to columns but I'd really like to know the formulae.
    I've got Column B sorted as:
    =IF(ISNUMBER(SEARCH(" ",A1)),LEFT(A1,SEARCH(" ",A1)-1),A1)
    I had to add the IF bit as not everyone has entered the model (the cell could just say BMW with no spaces etc.)
    I can't seem to get my head around the other two formula.
    Formula for C should be something like:
    Copy from cell after first space to next space or end of cell if no further spaces.
    Formula for D should be something like:
    Copy from cell after first space to end of cell.

    Hope you can help.

    Rgds


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'day,

    I think probably could just use the formula you already have in B1. For example in C1:

    =LEFT(RIGHT(A1,LEN(A1)-LEN(B1)-1),SEARCH(" ",RIGHT(A1,LEN(A1)-LEN(B1)-1)))

    And in D1:

    =RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-2)

    I suppose you might want to wrap an if statment around those to cover only partial entries.

    Hope that helps somewhat,
    Adam

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This should work for Col. C:

    =IF(ISNUMBER(SEARCH(" ",A1,SEARCH(" ",A1)))=TRUE,MID(A1,LEN(B1)+2,SEARCH(" ",A1,LEN(B1)+2)-LEN(B1)),"")

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, it should be:

    Col. A: =IF(ISNUMBER(SEARCH(" ",A1))=TRUE,LEFT(A1,SEARCH(" ",A1)-1),"")

    Col. B: =IF(ISNUMBER(SEARCH(" ",A1,SEARCH(" ",A1)))=TRUE,MID(A1,LEN(B1)+2,SEARCH(" ",A1,LEN(B1)+2)-LEN(B1)-2),"")

    Col. C: =IF(ISBLANK(C1)=FALSE,RIGHT(A1,LEN(A1)-SEARCH(" ",A1,LEN(B1)+LEN(C1)+1)),"")


    HTH

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could you copy and paste column A in a seperate sheet, save that sheet as a text file, and then open the sheet with spaces delimited?

    Being a board master I'm sure you've thought of that, so there must be a reason to have a formula in columns b-d.

    Therefore, I have written down a formula from an earlier post as it caught my eye.
    (The column C formula doesn't quite work, hopefully it will head you in the right direction.)

    Column B
    =left(a1,find(" ",A1)-1

    Column C
    =IF(ISERROR(FIND(" ",RIGHT(A10(FIND(" ",A10)-2)))),MID(A10,FIND(" ",A10)+1,FIND(" ",RIGHT(A10,FIND(" ",A10)))))

    Column D
    =right(A1,find(" ",A1)-1

    Kudos to whoever originally posted this formula.
    -Dave

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Thanks for all your help folks. I'm still not quite there though as I can't get the formula for Col C to work.
    Just to say where I'm up to know.

    Take the three possible type of entries in Column A:

    BMW
    BMW 320
    BMW 325 TI

    In Column B to strip our just the manufacturer I have:

    =IF(ISNUMBER(SEARCH(" ",A2)),LEFT(A2,SEARCH(" ",A2)-1),A2)

    In Column D to strip out everything but the manufacturer I have:
    =IF(ISNUMBER(SEARCH(" ",A3)),RIGHT(A3,LEN(A3)-LEN(B3)-1),"")

    I still can't get it to work for the middle column (C) though, where I only want the next entry after the manufacturer.

    For the example the answers would be:
    a blank cell for the just "BMW" entry
    a cell showing "320" for the "BMW 320" entry
    a cell showing "325" for the "BMW 325 TI" entry

    Thanks again.

    Rgds
    AJ

    BTW, this is not urgent, it's just something that intrigues me as to whether you can pick out the middle of a text string in this fashion. I know text to columns etc. etc. would work, I'm just trying to learn a bit more!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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