CODEWITHSUNDEEP
excelexcel-formulaworksheet-functionexcel-2003
Our Man in Bananas
Our Man in Bananas

Reputation: 5981

Excel TEXT format rounding issue

I am taking a string from a cell with my formula, and I need to output it (divided by 100 to show the PENCE) in the format "0.00" with the quotes!

For example: 975 becomes "9.75"

So I am using this formula:

TEXT(MID(C:C,17,FIND("_ _",C:C,12)-18)/10,"0.00")

and in the cell I expect to see 9.75 but it is being rounded, and I get 9.70.

Here is the source data (in column C":

323868_01064838_975_ _16092014_1.pdf

here is the complete formula including the TEXT conversion:

=IF(C:C<>"", """" & TEXT(MID(C:C,17,FIND("_ _",C:C,12)-18)/10,"0.00") & """","")

so what am I getting wrong here?

I need this so I can output it in csv format with the quotes for loading into another system.

Thanks for showing me what I am missing. Philip

Upvotes: 0

Views: 166

Answers (2)

user4039065
user4039065

Reputation:

It looks like the length and placement of the extraneous digits is fairly static but here is an alternate formula that will compensate for extra numbers here and there.

=CHAR(34)&0.01*TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),199,99))&CHAR(34)

             enter image description here

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

Change your "-18" to "-17" and divide by 100. E.g.

TEXT(MID(C:C,17,FIND("_ _",C:C,12)-17)/100,"0.00")

Your original MID() formula was only returning 97, not 975, so dividing by 10 gave you 9.7

Upvotes: 2

Related Questions