PDA

View Full Version : strip decimal point but keep cents



pdeshazier
04-21-2006, 07:33 AM
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
04-21-2006, 07:48 AM
Do a global Find and Replace, replacing . with nothing

pdeshazier
04-21-2006, 07:55 AM
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
04-21-2006, 08:08 AM
:bow: i got it. i had to change the decimal separator from . to a blank. Thanks for your help.

pdeshazier
04-21-2006, 08:14 AM
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
04-21-2006, 08:39 AM
Can't you just multiply your data by 100 before exporting to the text file?

lenze

pdeshazier
04-21-2006, 08:44 AM
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
04-21-2006, 08:52 AM
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
04-21-2006, 09:10 AM
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
04-21-2006, 10:02 AM
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
04-21-2006, 10:28 AM
Am I missing something? Isn't 0*100 still 0?

Yes, but the OP wants 0 to be a Blank

lenze

Bob Phillips
04-21-2006, 12:19 PM
Yes, but the OP wants 0 to be a Blank

lenze

But by doing nothing with it it stays as 0 :doh:

lenze
04-21-2006, 12:26 PM
But by doing nothing with it it stays as 0 :doh:
Maybe I'm missing something. Doesn't the line

Else: cl.Value = ""
fix that?

lenze