strip decimal point but keep cents

[pdeshazier]

From an excel file i receive, i'm to save as a .txt file and i need the decimal places in my text file, but not the "." How can i strip the period out of cells, while still maintaining the decimal places? thanks

[Bob Phillips]

Do a global Find and Replace, replacing . with nothing

[pdeshazier]

If you're talking about the Replace option in excel, i've tried that, but when i enter . at the find prompt, i keep getting 'microsoft office excel cannot find the data you're searching for.' Or, am i completely stupid and you're talking about doing this w/vb? thanks.

[pdeshazier]

i got it. i had to change the decimal separator from . to a blank. Thanks for your help.

[pdeshazier]

still didn't do it for me because i don't want the extra blank in there either. do you know how to read cell contents from right to left so i can find the decimal in the 3rd position and delete it?

[lenze]

Can't you just multiply your data by 100 before exporting to the text file?

lenze

[pdeshazier]

DUH!!! thanks, lenze. I also got it to work using SUBSTITUTE. I think i need to go back to bed and start over today..

[pdeshazier]

okay, now how can i code the worksheet to do all this when it opens? The * by 100 will require an IF statement to return a blank if the cell is 0, as the text file is supposed to only contain > 0 numbers (this is for a journal entry and is related to my 3/16 post, but i've had to resort to using a different source s/sheet than the one mentioned in that posting). so, what i need to do is:

1. open sheet and loop through the records, looking at certain cells in each record (ex. a1 & a2, b1 & b2, c1 & c2, etc) and for those numeric cells, apply my conditional * by 100, then OVERWRITE the contents of the cell with the value returned by the IF stmt. make sense?

[lenze]

Maybe something like this

Sub ConVert()
    Dim cl As Range
    For Each cl In Selection
    If cl.Value <> 0 Then
        cl.Value = cl.Value * 100
        cl.NumberFormat = "General"
        Else: cl.Value = ""
    End If
    Next cl
End Sub

Identify or define Selection as required.

lenze

[Bob Phillips]

Maybe something like this

Sub ConVert()
    Dim cl As Range
    For Each cl In Selection
    If cl.Value <> 0 Then
        cl.Value = cl.Value * 100
        cl.NumberFormat = "General"
        Else: cl.Value = ""
    End If
    Next cl
End Sub

Identify or define Selection as required.

lenze

Am I missing something? Isn't 0*100 still 0?

[lenze]

Am I missing something? Isn't 0*100 still 0?

Yes, but the OP wants 0 to be a Blank

lenze

[Bob Phillips]

Yes, but the OP wants 0 to be a Blank

lenze

But by doing nothing with it it stays as 0

[lenze]

But by doing nothing with it it stays as 0

Maybe I'm missing something. Doesn't the line

Else: cl.Value = ""

fix that?

lenze