Excel help please
Discussion
Assuming these are cells with text in them, rather than a fancy number format, then the quickest way would probably be to find & replace on that single column.
Find & replace " CR" with ""
Find & replace " DR" with ""
Find & replace "£" with ""
However, I assume you want the CR to be positives and the DR to be negative, so a bit of pre-working is required.
If you can sort them so all the DR entries are in one block, then you can do a concatenate =concatenate("-",A1) (where A1 is the cell you're applying the minus sign to) which will add a minus sign in front of the text already there, you just need to then copy & paste values to insert the corrected text and run your find & replaces.
Find & replace " CR" with ""
Find & replace " DR" with ""
Find & replace "£" with ""
However, I assume you want the CR to be positives and the DR to be negative, so a bit of pre-working is required.
If you can sort them so all the DR entries are in one block, then you can do a concatenate =concatenate("-",A1) (where A1 is the cell you're applying the minus sign to) which will add a minus sign in front of the text already there, you just need to then copy & paste values to insert the corrected text and run your find & replaces.
Edited by mmm-five on Wednesday 9th April 09:28
=RIGHT(MID(I101,1,(FIND(" ", I101)-1)),FIND(" ", I101)-2)
does the trick providing you have a <space> between the number and the DR or CR.
Sorry missed the DR vs. CR bit, just tried this and it works.
=IF(ISERROR((FIND("DR",I101))), VALUE(RIGHT(MID(I101,1,(FIND(" ", I101)-1)),FIND(" ", I101)-2)), VALUE(-1 * RIGHT(MID(I101,1,(FIND(" ", I101)-1)),FIND(" ", I101)-2)))
It's not pretty and I am sure there are better ways.
ETA: nice porker btw!
does the trick providing you have a <space> between the number and the DR or CR.
Sorry missed the DR vs. CR bit, just tried this and it works.
=IF(ISERROR((FIND("DR",I101))), VALUE(RIGHT(MID(I101,1,(FIND(" ", I101)-1)),FIND(" ", I101)-2)), VALUE(-1 * RIGHT(MID(I101,1,(FIND(" ", I101)-1)),FIND(" ", I101)-2)))
It's not pretty and I am sure there are better ways.
ETA: nice porker btw!
Edited by 2something on Wednesday 9th April 11:16
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff