This is what my input originally looked like.
Charge | |
Gasoline $21.12 Oil 18.98 | |
Charge | |
Oil 18.98 Coolant $11.63 |
This is what I got it to look like now
Charge | |
Gasoline $21.12 | |
Oil 18.98 | |
Gasoline $21.12 Oil 18.98 | |
Charge | |
Oil 18.98 | |
Coolant $11.63 | |
Oil 18.98 Coolant $11.63 |
I was able to separate the charges, i.e gasoline and oil, to be on separate rows but the original is still there. I used ReGex tool to tokenize and in the expression I just had [Alpha]+\s.\d+.\d{2} to grab the word and charge amount. This got rid of everything else though so than I used a Union tool to join the original and new table. Now I need to get rid of the old row that has both charges in one cell. (i.e Gasoline $21.12 Oil 18.98)
This is what I would like the final output to look like.
Labour | Labour Charge |
Gasoline | 21.12 |
Oil | 18.98 |
Oil | 18.98 |
Coolant | 11.63 |
I know how to split the labour from its charge by using money sign/space as delimiter. My problem right now is how to get rid of the old cell containing both labour charges. I tried using a multirow formula and using if row-1 contains what is in current row than null current row, but i am getting mal-informed if statement error. Thank you any help would be much appreciated
Best,
Solved! Go to Solution.
Hi @VanG I mocked up a workflow that produces the output you describe. Let me know what you think?
Sorry I forgot to mention that there are some values that are in separate cells already. So using the RegEx like you did gets rid of them and that is same as original issue I had.
So if it looks like this and you use the RegEx to tokenize it will get rid of Coolant and Oil.
Charge | |
Oil 34.13 Oil 23.45 | |
Charge | |
Coolant | $18.98 |
Charge | |
Oil | $12.31 |
Hi @VanG I amended the workflow.