Complex(?) formulae needed...

[AJ]

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:

Manufacturer<Space>Model<Space>AdditionalInfo
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

 

[Asala42]

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

 

[jrnyman]

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)),"")

 

[jrnyman]

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

 

[croweld89]

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

 

[AJ]

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!