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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 05:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com