Validation:Remove Decimal and Commas From Pasted Numeric Values In Columns

[milwphil]

Good afternoon!

I have a spreadsheet that has a fairly large amount of validation built in.

In some circumstances, users will cut and paste data into multiple cells in a column (which could be anywhere from 1-10,000 cells in the column). The data coming from the source document sometimes contains commas (i.e. 10,000) or decimals (10000.00) or both (10,000.00). Unfortunately, the application we're loading the data into has issues with commas and decimals.

Does anyone have a vb solution (macro) that will remove commas and decimals from numbers?

I have two columns ("M" and "N") that require the validation.

Any assistance would be greatly appreciated!!

Thanks in advance!
Phil

 

[edokhotnik]

I am not very proficient with vba, but I would select columns M & N and format the cells with Number, 0 decimals and uncheck the comma as the 000's delimiter. HTH

 

[milwphil]

Thanks for the response! However, that solution doesn't resolve the issue for data that is cut and pasted.

I was really hoping to find some vb code that will automate the process.

 

[milwphil]

Just doing some research it seems that a Clnt function is something that may do the trick, but I don't know how to use it....

Any suggestions???

Thanks in advance!

 

[CWatts]

Depending on how your data is formatted, if there is a cell that you kow will always be blank on the sheet, or perhaps another sheet, put the number 1 in there.

Copy that number one.

Highlight all the data on the sheet you want to "correct". Choose Paste Special, select multiply and click ok.

In the chance that these number are stored as text, it will convert them all to numbers. Then you can adjust the formatting to Number with 0 decimal places.

Sub SampleMacro()
    Sheets("BlankSheet").Select
    Range("A1").Select
    Selection.Copy
    Sheets("DataSheet").Select
    Range("A1:G14").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0"
End Sub

BlankSheet!A1 has the number 1 that I am copying.