Hi all
I'm just wondering if there is a way to format the monthly NL report from sage when exported into excel so that it doesn't have the DR and CR in front of the figures. I cant use Vlookup to get the figures from this report into my monthly accounts/ budget comparisons.
Many thanks
Susanne
Replies (3)
Please login or register to join the discussion.
Never used sage - but
If you have say DR12 and you want 12, then you could try highlighting the cells, use ctr h (or edit replace), type DR in find box and leave replace as blank. End result will be the number.
Hope it helps.
As the DR & CR are consistent
and have a space after them I believe, do
=RIGHT(cellref,LEN(cellref)-3)
You may need to do =VALUE(RIGHT(cellref,LEN(cellref)-3) ) to get a numeric answer.
OR...
=IF(LEFT(cellref,2)="CR",-VALUE(RIGHT(cellref,LEN(cellref)-3)) ,VALUE(RIGHT(cellref,LEN(cellref)-3)) )
Check the brackets but this will give you DRs and CRs as + & -