help reducing this long nested functuion?
 

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&F 8&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&K 8&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?

help reducing this long nested functuion?
 

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

--
Rick (MVP - Excel)


"Herald" wrote in message
...
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&F 8&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&K 8&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?

help reducing this long nested functuion?
 

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


"Herald" wrote:

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&F 8&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&K 8&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?

help reducing this long nested functuion?
 

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"Herald" wrote:

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&F 8&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&K 8&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?