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
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