method to seperate a singel cell into 2 different cells

[WGGMKG]

here's what i have to figure out, with your help.

i have a list:
A colm Bcolm
order a delivered 10/10/06
order b delivered 10/09/06
order c in transit 10/10/06
order d in transit 10/11/06

I want to take this list and put them in a pivot table, but i only want to select either in transit or delivered.

is there a way to seperate the delievered and in transit form the rest of the contents of the cell?
I can't use =left() because the length of info to pull differs.
help!

 

[awalle]

A simple way is using an if, like

=If(upper(mid(b1,1,5))="DELIV", "Delivered", If(upper(mid(b1,1,5))="IN TR", "In Transit", b1))

Put this formula over C column, this will separate the status, then you can use this column in your pivot table.

Hope this can help you
AWalle.

 

[barry houdini]

perhaps

=RIGHT(B1,LEN(B1)-9)

 

[WGGMKG]

this worked great...Thanks.