Creating number

[Aero]

I'm trying to find an expression for something like this :



The left column are the thousands, then the hundreds, then the dozens,...
(i think you get the picture)

Now i could do this like so:

=A1*1000+B1*100+C1*10+D1*1

However there is a problem:
- the number has a variable length, it could be only '13', but could also be '15646847'
( in the beginning i will know the length of the number, for instance: 100-999 or 1000-9999 , ...)

( the equation signal is for illustration purposes only, it's actually like this: 0-2-4-0-240 )

Can someone push me in the right direction?
(i can do it using VBA / normal expressions in Excel)


EDIT: after some thinking it's actually not that hard to achieve:
(somewhat edited from my situation)
[vba]Dim index As Integer
Dim counter As Integer
Dim expression As String

counter = WorksheetFunction.Power(10, lengthNumber)
expression = "="

For index = 1 To lengthNumber
expression = expression & Cells(index, index).Address(False, False) & "*" & counter & "+"
counter = counter / 10
Next index

expression = expression & "0"
ws.Range(lengthNumber + 1).Value = expression[/vba]

[tpoynton]

This might work too for a non-vba solution, although it seems you've solved it.

[VBA]=CONCATENATE(A1,B1,C1,D1)*1[/VBA]

[Aero]

This might work too for a non-vba solution, although it seems you've solved it.

[vba]=CONCATENATE(A1,B1,C1,D1)*1[/vba]

Yes, i had also thought of that. There's 2 problems (one i didn't mention):

if the data is like this : 0 - 2 - 4 - 0
it will give me : 0240 instead of 240

The second problem is that the cells arent actually always filled, some may be blank, like this: 2 - 4 - - -
which should give me 2400

Thanks for the help anyway !

[Bob Phillips]

Try

=--(A1&B1&C1&D1&E1&G1)

[Kenneth Hobs]

XLD's formula will work with the Text() function. Of course this gets tedious for large ranges. A UDF would then be best.

=--CONCATENATE(TEXT(A1,"0"),TEXT(B1,"0"),TEXT(C1,"0"),TEXT(D1,"0"))