Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a # w/decimal places to no decimals, 13 char's, w/leading 0
To import data into SAP from Excel, I need to create a load file that
converts a column w/positive or negative values & decimal places into all positive values with no decimal places, and with leading zeroes--to fill out 13 places. For example: -74737.76069 needs to be converted to 0000747376069 and 105286.99 needs to be converted to 0000010528699 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a # w/decimal places to no decimals, 13 char's, w/leading 0
Are the resulting values numeric or character. If numeric you can create a
custom format that pads cells with 0's. The format would be "00000000000000" so that any position not having a corresponding number will display a 0 instead. If it's a string the following function might be of some assistance: Function ConvertVals(Value As Double) As String Dim strVal As String Dim i As Integer Application.Volatile If Value < 0 Then Value = Value * -1 strVal = CStr(Value) strVal = Replace(strVal, ".", "") i = Len(strVal) Do Until i = 13 strVal = "0" & strVal i = Len(strVal) Loop ConvertVals = strVal End Function -- Kevin Backmann "Mary" wrote: To import data into SAP from Excel, I need to create a load file that converts a column w/positive or negative values & decimal places into all positive values with no decimal places, and with leading zeroes--to fill out 13 places. For example: -74737.76069 needs to be converted to 0000747376069 and 105286.99 needs to be converted to 0000010528699 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a # w/decimal places to no decimals, 13 char's, w/leading 0
If you wanted to use a formula, you could try something like this.
=IF(LEN(SUBSTITUTE(SUBSTITUTE(A1,".",""),"-",""))<13,(REPT("0",13-LEN(SUBSTITUTE(SUBSTITUTE(A1,".",""),"-","")))&SUBSTITUTE(SUBSTITUTE(A1,".",""),"-",""))) This is with your original value in A1. HTH, Paul "Mary" wrote in message ... To import data into SAP from Excel, I need to create a load file that converts a column w/positive or negative values & decimal places into all positive values with no decimal places, and with leading zeroes--to fill out 13 places. For example: -74737.76069 needs to be converted to 0000747376069 and 105286.99 needs to be converted to 0000010528699 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a # w/decimal places to no decimals, 13 char's, w/leading 0
On Mon, 30 Jan 2006 08:41:49 -0800, "Mary"
wrote: To import data into SAP from Excel, I need to create a load file that converts a column w/positive or negative values & decimal places into all positive values with no decimal places, and with leading zeroes--to fill out 13 places. For example: -74737.76069 needs to be converted to 0000747376069 and 105286.99 needs to be converted to 0000010528699 Assuming your first example has a typo, then: =TEXT(SUBSTITUTE(A10,".",""),REPT("0",13)&";"&REPT ("0",13)) (returns 0007473776069 and not 0000747376069) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decimal places and leading zeros | Excel Discussion (Misc queries) | |||
How to change complex 0.122345687+0.87659454j to 3 decimal places | Excel Discussion (Misc queries) | |||
how do I change default decimal places? | Excel Discussion (Misc queries) | |||
How to change decimal places in balance sheet template | Excel Worksheet Functions | |||
How do I change the number of decimal places displayed in Excel | Excel Discussion (Misc queries) |