help reducing this long nested functuion?

[Herald]

I was making a binary converter in Excel 2007, a challange from my teacher,
and I found that using multiple IF functions inside a TEXT function I can
turn a group of cells into one data entry.

The full function is
=TEXT(IF(C8>=1,C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8,IF(D8>=1,D8&E8&F8&G8&H8&I8&J8&K8&L8&M8,IF(E8>=1,E8&F8&G8&H8&I8&J8&K8&L8&M8,IF(F8>=1,F8&G8&H8&I8&J8&K8&L8&M8,IF(G8>=1,G8&H8&I8&J8&K8&L8&M8,IF(H8>=1,H8&I8&J8&K8&L8&M8,IF(I8>=1,I8&J8&K8&L8&M8,IF(J8>=1,J8&K8&L8&M8,IF(K8>=1,K8&L8&M8,IF(L8>=1,L8&M8,M8)))))))))),0)

The refernces refer to the 10 cells in which digits of a number can be
stored and the IFs are used to stop 0s being placed in front of the
digits>=1. I had to use all of the & signs because the TEXT function wouldn't
recognise entries such as C8:M8, and I wanted to reduce the number of IF
functions so it would save as a 2003 file.

Can anyone help?

 

[Rick Rothstein]

You will have to tell your teacher you got this online (he/she will be able
to check); besides, he/she will know you didn't come up with this on your
own.

Okay, with that said... how about not using any IF function calls at all?

=TEXT(--(C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8),"0")

 

[Jacob Skaria]

Does this work?

=INT(TRIM(C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8))

OR this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=concat(C8:M8)

Function CONCAT(varRange As Range) As String
Dim blnStart As Boolean
For Each cell In varRange
If blnStart = False Then
If cell >= 1 Then blnStart = True
End If
If blnStart = True Then CONCAT = CONCAT & cell
Next
If CONCAT = vbNullString Then CONCAT = cell
End Function



If this post helps click Yes

 

[Rick Rothstein]

See inline comments...

Does this work?

=INT(TRIM(C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8))

Or, without using function calls...

=--(C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8)

However, that returns a number and I assumed from the structure of his
posted formula that he wanted the result as Text (which I'm guessing you
recognize because your UDF returns a String) which is why I wrapped that in
a TEXT function call in my response to this question.

OR this UDF (User Defined function). From workbook launch VBE using
Alt+F11.
From menu Insert a Module and paste the below function.Close and get back
to
workbook and try the below formula.

=concat(C8:M8)

Function CONCAT(varRange As Range) As String
Dim blnStart As Boolean
For Each cell In varRange
If blnStart = False Then
If cell >= 1 Then blnStart = True
End If
If blnStart = True Then CONCAT = CONCAT & cell
Next
If CONCAT = vbNullString Then CONCAT = cell
End Function

Or, as a one-liner...

Function CONCAT(varRange As Range) As String
CONCAT = CLng(Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(varRange)), ""))
End Function