Change a # w/decimal places to no decimals, 13 char's, w/leading 0

[Mary]

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

[Kevin B]

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
>
>
>

[PCLIVE]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[Ron Rosenfeld]

On Mon, 30 Jan 2006 08:41:49 -0800, "Mary" <[email protected]>
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