1

I am using Excel 2007. I have multiple data sets with random number of characters in each row. For example: 

A1 1.60 

A2 0.008 

A3 0.900 

A4 1.0 

A5 0.56 

A6 1.703

I need to make it into a different order on a different page such as 

A1 1.60

A2 0.900

A3 1.0

A4 0.56

A5 1.703

A6 0.008

Unfortunately, whenever I move it to a new page (example, I can move it with =Page1!A1) the numbers revert to

A1 1.6

A2 0.9

A3 1

A4 0.56

A5 1.703

A6 0.008

So I lose the zeros.

To complicate things, the number of characters of each entry/row/column varies between data sets. This means that using =TEXT(A1,"#.#0") can't work -- sometimes my A1 could be 1498 or other.

I am (potentially) looking for code that will 'count' the number of decimals shown, and then produce that automatically. Or any other ways to get the number of decimals (or lack there of) correct for my variable data will do. VBA/Macros/Functions?

Community
  • 1
  • 1
user2347410
  • 11
  • 3

1 Answers1

0

You can adapt this code to your situation I think...

Public Sub MaintainTrailingZeroFormat()
Dim s As String
Dim lngLength As Long, lngDecimal As Long, lngTrailingZero As Long
Dim r As Range
''Grab cell value as text
s = ActiveWorkbook.ActiveSheet.Range("A2").Text
''Find decimal from beginning of string
lngDecimal = InStr(s, ".")
''Find total length of string
lngLength = Len(s)
''Subtract to find number of trailing zeros
lngTrailingZero = i - st
''Set destination cell format to 'TEXT'
ActiveWorkbook.Sheets(2).Range("A1").NumberFormat = "@"
''Populate cell with text value
ActiveWorkbook.Sheets(2).Range("A1") = FormatNumber(s, EX)

End Sub
Marshall
  • 99
  • 5