Excel help please

[LeoSayer]

I have a column of numbers in the following format which I need to convert to actual values which I can sum on. I've tried using LEFT, RIGHT and MID functions but can't seem to get what I want:

£6.26 DR
£60.00 DR
£16.07 DR
£61.25 DR
£1.23 CR
£40.00 DR
£5.26 DR
£100.30 DR
£5.99 CR

[mmm-five]

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.

Edited by mmm-five on Wednesday 9th April 09:28

[2something]

=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!

Edited by 2something on Wednesday 9th April 11:16

[LeoSayer]

Superb! Cheers 2s.

[miles_stylus]

same sort of idea, my calc would be:
=if(right(a1,2)="CR",mid(a1,2,find(" ",a1)-1)*1,-mid(a1,2,find(" ",a1)*1)

Hope this helps

Miles

[wiggy001]

Bit late I know but this is neater:

=IF(RIGHT(A1,2)="CR",MID(A1,2,LEN(A1)-3),MID(A1,2,LEN(A1)-3)*-1)