Find & Replace Macro

[Steve K]

A need for a macro that is beyond my ability....This macro is to be used in a
csv file.

In Column "C" I have Part numbers that are formated as 00000000 or
50004590...these do not cause me any issues...and should be left alone.

But in Column "C" I can also have part numbers that are formated as 000.0000
or 440.4300. Excel insists on removing any trailing 0's when opening and
saving a CSV file...whenever it sees a decimal point in Part Number.

I'd like a macro that scans column "C" and when it finds a Part number that
equals a 000.0000 format it will convert it to text and add any missing
trailing 0's (up to four).

My plan is to save as CSV but not re-open in Excel or save, which (of
course) would again strip training 0's.

Thanks in advance....Steve

 

[Bernie Deitrick]

Steve,

Simply rename your CSV file as a TXT file, and in the dialog that opens when
you try to open the TXT file, set your column C to be text and Excel will
ignore the .0000 issue.

HTH,
Bernie
MS Excel MVP

 

[J_Knowles]

Put this macro in a separate spreadsheet (not in the csv file). Run the
macro and then save the csv file. The formatting you want should be saved in
the csv file.

Sub ValuesForCSV()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For x = lastrow To 1 Step -1 'change To 2 if there is a header row
If Cells(x, 3).Value > 10000000 Then
Cells(x, 3).NumberFormat = "General"
Else
Cells(x, 3).NumberFormat = "#.0000"
End If
Next
End Sub

 

[Steve K]

J - Thanks for marco help...it works perfectly. Earlier post by Bernie also
was a solution, but yours allows for less "messing" around by our group. It
simplify's the process significantly. Thanks Much !

Steve