Excel to retain trailing decimal zeros

Hi Henno,

You can format the cells where you got that "problem" like
-rightclick
-format cells
-number and the format you want

or do you mean something different?

:O)Bruintje

Hehe,

Sorry if I tricked a bit but I was afraid that if I make the question text too long then nobody would bother to read it.

Anyways:

Reason for this is because I'm scanning invoices and importing them to Excel. But Excel automatically loses the decimal separator and all the trailing zeros. And furthermore: those invoices have nasty habit of not showing the leading decimal separator if some "unit price" field value is under $1:

QTY       Price
1,0        6,94
5,0          58
1,0       12,00

The "58" there means 58 cents. I could add "0," in front of every 2-digit cell but since Excel makes "12" out from "12,00" I really can't programmatically tell which one of "58" and "12" is in cents and which one is integer dollars :)

So what's the big deal you say I'm sure. :)

Well, I can't preformat it myself because scanner application automatically spawns a new worksheet and dumps it's data there. That's the catch. :(

Henno

I've heard about personal.xls. If I open up personal.xls and change cells' properties to text there... will it work then?

P.S.
I can't find that personal.xls on my system. Using WinXP and OfficeXP.

Hi Henno,

so to stay with your sample "58" is always cents? and 12,00 is always dollars?

can the scanner app throw the data into csv or txt? then we could try and get it as text from there

:O)Bruintje

Actual digits vary but yeah "xx" is always cents and "x,xx" is always dollars. No csv, three selections only: "Word, Excel, Wordpad"

Henno

but you mentioned he lost the ,00 in 12 so you end up with "58" and "12" that's really frustrating because how do you now that that 12 was 12,00 without lookin at the answers?

if you export to word what will the format be? a table with values maybe you can save it as html from within Word and import the html into excel as text

yeah i know this is getting a long round but how can we distuingish between the two "58" and "12" if they end the same directly in excel

:O)Bruintje

Consider export to Wordpad or Word, rename the extension to TXT and see if Excel's text import wizard gives you some better options.

http://fire.japauto.ee/VV.doc

Here's Word version with original-size decimal places.

no go. Page cannot be displayed.

http://fire.japauto.ee/vv.zip

I've added both versions: When Word was specified as output and when Excel was specified as output.

I've tried to copy the table in Word and paste it to Excel but no matter what I do Excel still stubbornly cuts off trailing zeroes along with commas.

Henno

Hi Henno,

What i did was open the

-word version and save as text
-open in excel
-this ruined the table
-but with a little macro we can rebuild that one
-at least the numbers where saved

:O)Bruintje

Does it come through exactly as the Word file displays? If so, you're going to be in trouble anyway as the data is not valid. For instance, one total has 11,68 and the original cell has 11,<space>68

Dreamboat there are more of them like that, but a loop through it could take that into account

Dreamy doesn't do code. :)

henno,

Let's see if I understand your problem correctly by this possible solution.  I copied the last 5 column of cells from your table document (vv.doc) into a new Word document from the "Separator" row down to the "C.O.N" row.  In this new document perform the following replacements:

first, the first cell has a letter "O" in it that should be replaced with a zero (actually even after I finished I still had a problem with this cell reading as a text value of 1.0 but do this replacement anyway as it will help later if you simply paste special multiply it by 1 or just type in the value 1.0 itself in that cell);

then perform the following replacements in order using the Edit, Replace command:
a) space with nothing;
b) period with nothing;
c) slash with comma;
d) paragraph mark with nothing;
e) comma with period.

Then select the table thru Table, Select table, Edit, Copy and Paste the table into Excel (start in column D as you'll need A,B & C for the first three columns).  This will result in a column of whole numbers in the first column, a blank second column, zeros in the fourth column and numbers with decimal places in columns three and five.

*****
I don't know if you need to differentiate items for the first column with the whole numbers, but to edit things for the third and fifth columns place the formula below in an blank column.  My numbers started in cell F3:
=IF(ISERROR(SEARCH(".",F3)),("."&F3)*1,F3)
Copy this formula down the column as needed.
*****

The paragraph above between the asterisks is to satisfy your condition: "The "58" there means 58 cents."

Then after that you can use the copy and paste special values options as needed to copy your values to cells as numbers without the underlying formulas.

The attempt I provide here assumes two very important concerns:
a) every number with a comma is a whole number and the comma is the beginning of the decimal portion of the number; and
b) if there is no comma within the number its value is less than $1.

Now select the first three columns in the original Word table, copy them into another Word document, then perform an Edit, Replace with the paragraph marks with nothing.  Copy and paste the items in the first three columns.  This will effectively transfer all your data into Excel format as desired (if as I stated earlier, I understand your problem correctly).

Hope this helps.

Hmm...

I got sick today and I'm at home right now. I have no Excel or Word here so I can't try this.

I will reread the last post and try it at work tomorrow.


Some background information
-----------------------------
In every few days some invoices come from abroad.

Issue: Logistics department must convert prices on those invoices to local currency.

So far they have done this by hand: they use a Word document with a carefully positioned Excel object embedded within it. They open the embedded worksheet and enter the "Unit price" field's values to column "A" and then they'll have local prices in column "B". Width of the embedded object is the width of 1 worksheet column. When they exit the embedded object, they leave the local prices' column shown. Then they put the original invoice in the printer and print the local prices' column between the two last columns on the invoice.

Then they'll send the invoice to another person who checks if the prices in the local pricelist and on the invoice match.

I was given a task to automate this process. The company bought a scanner for that and I need to develop a system of some kind. I was hoping that I can get away with scanner application dumping the data straight to Excel and then run a macro over it, doing the replacements and some formatting. I almost got away with it, too. The only problem left that I can't tell when unit price is below $1 (e.g. $0.58) or it's exactly even dollars ($58.00) since while scanner app dumps the data, Excel does some hocus-pocus and "simplifies" numbers and so "58" stays 58 and "58,00" becomes "58" too.

I will try to change decimal separator in Control Panel to some kind of other symbol and then try to import. Then Excel should not think that the comma is decimal separator.
After that I could add the "0," in front of all the two digit numbers and the do search and replace and replace all the commas with new decimal separator. Since the scanner computer does nothing but sits and hosts scanner the decimal separator change shouldn't be a problem.

Cheers,
Henno

Hi Henno and all,

a bit of code to help move the big table to excel from word
-open your exported word document
-open the VB Editor in Word
-insert a new module
-paste the code
-and run

Sub ExportTable2XL()
Dim i As Integer
Dim j As Integer
Dim strVal As String
Dim c As Cell
Dim oExcel As Object
Dim oXLTable As Worksheet
Dim rw As Integer
  i = 1
  j = 1
  strVal = ""
 
  Set oExcel = CreateObject("Excel.Application")
  oExcel.Visible = True
  oExcel.Workbooks.Add
  Set oXLTable = oExcel.Workbooks.Item(1).Worksheets(1)
 
  ActiveDocument.Tables(1).Select
  rw = Selection.Rows.Count
  For i = 1 To rw
    j = 1
    For j = 1 To 8
      strVal = Trim(ActiveDocument.Tables(1).Cell(i, j).Range.Text)
      'special handling of the amount values
      If j = 4 Then
        If (InStr(1, strVal, ",0") <> 0 And i > 1) Then strVal = Left(strVal, Len(strVal) - 2)
        If (InStr(1, strVal, "/0") <> 0 And i > 1) Then strVal = StrFindReplace(strVal, "/0", "", True)
        oExcel.Workbooks.Item(1).Worksheets(1).Cells(i, j).Value = Left(strVal, Len(strVal) - 3)
      'special handling of the price values
      ElseIf j = 6 Then
        If (InStr(1, strVal, ",") = 0 And i > 1) Then strVal = "0," & strVal
        If (InStr(1, strVal, ", ") <> 0 And i > 1) Then strVal = StrFindReplace(strVal, ", ", ",", True)
        oExcel.Workbooks.Item(1).Worksheets(1).Cells(i, j).Value = (Left(strVal, Len(strVal) - 3))
      Else
        oExcel.Workbooks.Item(1).Worksheets(1).Cells(i, j).Value = Left(strVal, Len(strVal) - 3)
      End If
    Next
  Next
End Sub

but it is still text values, got a txt version also but can't figure out why the seperator doesn't work in an excel import

Sub ExportTable2CSV()
Dim i As Integer
Dim j As Integer
Dim strVal As String
Dim c As Cell
Dim iFile As Integer
Dim strLine As String
Dim strFileName As String
Dim rw As Integer
  i = 1
  j = 1
  strVal = ""
 
  strFileName = "c:\table.txt"
 
  iFile = FreeFile
 
  Open strFileName For Output As #iFile
 
  ActiveDocument.Tables(1).Select
  rw = Selection.Rows.Count
  For i = 1 To rw
    j = 1
    For j = 1 To 8
      strVal = Trim(ActiveDocument.Tables(1).Cell(i, j).Range.Text)
      'special handling of the amount values
      If j = 4 Then
        If (InStr(1, strVal, ",0") <> 0 And i > 1) Then strVal = Left(strVal, Len(strVal) - 2)
        If (InStr(1, strVal, "/0") <> 0 And i > 1) Then strVal = StrFindReplace(strVal, "/0", "", True)
        strLine = strLine & ";" & Left(strVal, Len(strVal) - 3)
      'special handling of the price values
      ElseIf j = 6 Then
        If (InStr(1, strVal, ",") = 0 And i > 1) Then strVal = "0," & strVal
        If (InStr(1, strVal, ", ") <> 0 And i > 1) Then strVal = StrFindReplace(strVal, ", ", ",", True)
        strLine = strLine & ";" & (Left(strVal, Len(strVal) - 3))
      Else
        strLine = strLine & ";" & Left(strVal, Len(strVal) - 3)
      End If
    Next
    Write #iFile, strLine
    strLine = ""
  Next
 
  Close iFile

End Sub



Public Function StrFindReplace(ByVal cSrc As String, ByVal cFind As String, _
ByVal cReplace As String, Optional ByVal vFirstOnly As Variant) As String

  '----------------------------------------------------------------------
  'PURPOSE    : Searches a given string and replaces all or the first
  '             occurences of the "cFind" parameter with "cReplace".
  '
  'PARAMETERS : cSrc       ==> String  - Source string
  '             cFind      ==> String  - Data to be replaced
  '             cReplace   ==> String  - Replacement value
  '             vFirstOnly ==> Variant - Optional flag to indicate if
  '                                      all or only the first occurence
  '                                      needs to be replaced.
  '
  'RETURNS    : String
  '----------------------------------------------------------------------
  Dim nPos As Integer
  Dim cRet As String
  Dim nStart As Integer

  If (IsMissing(vFirstOnly)) Then
    vFirstOnly = False
  End If

  On Error GoTo strFindReplceErr

  nStart = 1
  cRet = cSrc
  Do While (True)
    nPos = InStr(nStart, cRet, cFind)
    If (nPos > 0) Then
      nStart = nPos + Len(cReplace)
      cRet = Left(cRet, nPos - 1) & cReplace & Mid(cRet, nPos + Len(cFind))
      If (vFirstOnly) Then
        Exit Do
      End If
    Else
      Exit Do
    End If
  Loop

  On Error GoTo 0

  StrFindReplace = cRet
  Exit Function

strFindReplceErr:
  StrFindReplace = cSrc
  Exit Function

End Function

may be this helps a bit

:O)Bruintje

bad me, i didn't read your last post, is the code something you can work with and should i add some the other parts of the invoice to it?

What interests is only the table on the invoice. Other data is irrelevant.

Hmm.



> but it is still text values, got a txt version also
> but can't figure out why the seperator doesn't work
> in an excel import
What is still text values? The data in worksheet cells? This can be "fixed" by iterating through cells overwriting values:

For Each cl In Range(<tablerange>)
    cl.Formula = Val(cl.text)
Next

Only question is: how to inject that code into newly created Excel object and execute it there? ;)

Henno

OK here's a sort of answer then :)

Sub ExportTable2XL()
Dim i As Integer
Dim j As Integer
Dim strVal As String
Dim c As Cell
Dim oExcel As Object
Dim oXLTable As Worksheet
Dim rw As Integer
Dim comPos As Integer
  i = 1
  j = 1
  strVal = ""
 
  Set oExcel = CreateObject("Excel.Application")
  oExcel.Visible = True
  oExcel.Workbooks.Add
  Set oXLTable = oExcel.Workbooks.Item(1).Worksheets(1)
 
  ActiveDocument.Tables(1).Select
  rw = Selection.Rows.Count
  For i = 1 To rw
    j = 1
    For j = 1 To 8
      strVal = Trim(ActiveDocument.Tables(1).Cell(i, j).Range.Text)
      'special handling of the amount values
      If j = 4 Then
        If (InStr(1, strVal, ",0") <> 0 And i > 1) Then strVal = Left(strVal, Len(strVal) - 2)
        If (InStr(1, strVal, "/0") <> 0 And i > 1) Then strVal = StrFindReplace(strVal, "/0", "", True)
        oExcel.Workbooks.Item(1).Worksheets(1).Cells(i, j).Value = Left(strVal, Len(strVal) - 3)
      'special handling of the price values
      ElseIf j = 6 Then
        If (InStr(1, strVal, ",") = 0 And i > 1) Then strVal = "0," & strVal
        If (InStr(1, strVal, ", ") <> 0 And i > 1) Then strVal = StrFindReplace(strVal, ", ", ",", True)
        comPos = InStr(1, Left(strVal, Len(strVal) - 3), ",")
        oExcel.Workbooks.Item(1).Worksheets(1).Cells(i, j).Value = Val(Left(strVal, Len(strVal) - comPos)) + Val(Right(strVal, Len(strVal) - comPos)) / 100
      'special handling of the total price values
      ElseIf j = 8 Then
        comPos = InStr(1, Left(strVal, Len(strVal) - 3), ",")
        oExcel.Workbooks.Item(1).Worksheets(1).Cells(i, j).Value = Val(Left(strVal, Len(strVal) - comPos)) + Val(Right(strVal, Len(strVal) - comPos)) / 100
      Else
        oExcel.Workbooks.Item(1).Worksheets(1).Cells(i, j).Value = Left(strVal, Len(strVal) - 3)
      End If
    Next
  Next
End Sub

only the last value in the total price column is wrong when converted to value, someway it changes 1347.57 into 1915 if you take the values seperate before adding you see 1347 and 0.57 if i do 1347 + 0.57 Excel thinks it's 1915

a little known bug? but it is only the last value strangely enough

:O)Bruintje

What interests is only the table on the invoice. Other data is irrelevant.

Hmm.



> but it is still text values, got a txt version also
> but can't figure out why the seperator doesn't work
> in an excel import
What is still text values? The data in worksheet cells? This can be "fixed" by iterating through cells overwriting values:

For Each cl In Range(<tablerange>)
    cl.Formula = Val(cl.text)
Next

Only question is: how to inject that code into newly created Excel object and execute it there? ;)

Henno

I read the read vertically (i.e. with Edit/Find) so I am risking a blunder:

Why not formatting the target cells as 'Currency' or 'Accounting' ?

Hi,

Just a suggestion,
What are your country code settings?

In some countries , is the decimal separator and . is the thousands separator.

In this instance it appears that your application is using , as decimal, while excel is using it as thousands ... or something along that line.

I like Angelll's suggestion to take it trhough notepad/wordpad.

Maybe a macro is needed if the problem is comma space that could simply be replaced out ...

ie
53, 00 ==> 5300
00, 12 ==> 0012

Regards
  David

The coutry is Estonia and yes, comma is decimal separator in Regional Options and dot and space are thousands separators. And so is also on the invoice. But the problem is not what scanner application thinks. Scanner application doesn't think anything. It just passes data, let it be comma or space, it just passes that data to Excel and now, the problem is that Excel thinks too much. Excel looses trailing zeroes after decimal separator. It wouldn't be a problem if the invoice didn't contain two-digit integers which are supposed to be fraction of currency unit. But it is because it does. And so "10,00" becomes "10" in Excel and "10" stays "10" too. On the invoice, the former means ten dollars but the latter means ten cents. So there's the problem.

did the macro help or not?

It tells me this: http://fire.japauto.ee/images/pic001.jpg

bruintje, are you still with me?

henno,

I still have to ask did you try the manual solution I posted earlier?  It worked for me perfectly with the data you presented in your earlier file.  This even though you had a number of anomalies in the file itself.  If the process works fine, automating it shouldn't be much trouble as it is a series of Edits and Replace(ment)s.  I'm not very good at code and avoid it if possible, but I'm sure if it works someone could try to automate the process for you.

Hope this helps.

Henno, sorry got no updates on this one, guess it got lost in the email trouble last weekend


be sure to do thsi in word

-choose tools | references | check ms excel

i'll post a sample for you later to download

:O)Bruintje

---------------------------
Hi henno,

Any luck yet to try the suggestions in this thread?
Please provide some feedback to the experts willing to spend their time and try to solve your problem.

---------------------------
Thanks
:O)Bruintje
---------------------------

Good job. That sample did work brilliantly. :)

great, glad to be of help, and thanks for the grade