method to seperate a singel cell into 2 different cells

WGGMKG

New Member
Joined
Sep 7, 2006
Messages
11
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56
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.
 

Forum statistics

Threads
1,141,930
Messages
5,709,401
Members
421,635
Latest member
mehdi hannechi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top