MycostPro Forum
Categories => General Discussion => Topic started by: ssheldon on April 04, 2019, 10:20:44 am
-
Hi Scott,
I need some help with a few formulas. I am trying to extract data from the SKU column into 3 other columns:
Item Purchase Price
Inventory Source
Purchase Date
Here are 2 examples of my SKU's. As you can see the number of characters in each section varies depending on the item, so I can't use the position in the formula. (I need the item type (ie: CD, TXBK) in the beginning of the SKU for my repricing templates).
CD-2.00-BKCAFE-01.05.2019-01
TXTBK-13.00-STVIN-01.05.2019-101
On page 1 of this original topic I was using this formula to pull the Item Purchase Price from the SKU (see below),
2.00-BKCAFE-01.05.2017
=VALUE(LEFT(G3,FIND("-",G3)-1))
but now I need to add the item type to the beginning of the sku and also pull out Inventory Source and Purchase Date. Spent the better part of last night and this morning googling and testing with no luck.
Can you help? Thanks!
Sue
-
I hope this helps.
Basically its like Find "Inception". Find within a find within a find...
For these examples "TXTBK-13.00-STVIN-01.05.2019-101" would be in cell A1:
=LEFT(A1,FIND("-",A1, 1)-1)
=MID(A1, FIND("-",A1, 1)+1, FIND("-",A1, FIND("-",A1, 1)+1)-FIND("-",A1, 1)-1)
=MID(A1, FIND("-",A1, FIND("-",A1, 1)+1)+1, FIND("-",A1, FIND("-",A1, FIND("-",A1, 1)+1)+1)-FIND("-",A1, FIND("-",A1, 1)+1)-1)
=MID(A1, FIND("-",A1, FIND("-",A1, FIND("-",A1, 1)+1)+1)+1, FIND("-",A1, FIND("-",A1, FIND("-",A1, FIND("-",A1, 1)+1)+1)+1)-FIND("-",A1, FIND("-",A1, FIND("-",A1, 1)+1)+1)-1)
=RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1,FIND("-",A1,FIND("-",A1,1)+1)+1)+1))
-
Winding down for the night and don't want to mess with it until I'm not so darn tired, but had to try the first one. Success... woo hoo!! Many thanks!