Build string from cells with ignoring blank

[FryGirl]

I’m trying to build a text string out of some cell, but Idon’t want the blank cells to appear.

In the first table I have an a-value and two x-values. Pretty easy to create the sting.

123.45 + 15.34(x1) + 12.44(x2)
=B1&" +"&B2&"("&A2&") +"&B3&"("&A3&")"

Data Range

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

a​

[/TD]
[TD]

123.45​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

x1​

[/TD]
[TD]

15.34​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

x2​

[/TD]
[TD]

12.44​

[/TD]
[/TR]
</tbody>[/TABLE]

In the second table there is only one x-value. Is there a way to ignore the blank cells andjust get the one x-value?
15.34(x1)

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

a​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

x1​

[/TD]
[TD]

15.34​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

x2​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Another option if you're interested

Function FryGirl2(Rng As Range) As String
   Dim Cl As Range
   For Each Cl In Rng
      If Cl.Value <> "" Then
         FryGirl2 = FryGirl2 & Cl.Value & "(" & Cl.Offset(, -1).Value & ")" & " + "
      End If
   Next Cl
   FryGirl2 = Replace(Left(FryGirl2, Len(FryGirl2) - 3), "(a)", "")
End Function

Used like
=FryGirl2(B1:B11)

 

[FryGirl]

Thank you Fluff. This is awesome as well. I actually misspoke where the a-value is. The a-value is in B11 and the data actually starts in B1:B10.

Expected result: 112 + 15.34(x1)

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

x1​

[/TD]
[TD]

15.34​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

x2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

x3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

x4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

x5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

x6​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

x7​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

x8​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

x9​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

x10​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

a​

[/TD]
[TD]

112​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[FryGirl]

I think I figured it out using Fluff's sample. I added the IF statement and added a Rng2.

​

[COLOR=#000000]FunctionFryGirl3(Rng As Range, Rng2 As Range) As String[/COLOR]
[COLOR=#000000]    Dim Cl As Range[/COLOR]
[COLOR=#000000]    For Each Cl In Rng[/COLOR]
[COLOR=#000000]        If Cl.Value <> "" Then[/COLOR]
[COLOR=#000000]            FryGirl3 = FryGirl3 &"(" & Cl.Value & Cl.Offset(, -5).Value & ")"& " + "[/COLOR]
[COLOR=#000000]        End If[/COLOR]
[COLOR=#000000]    Next Cl[/COLOR]
[COLOR=#000000]    If Rng2.Value <> "" Then[/COLOR]
[COLOR=#000000]        FryGirl3 = "Y = [" &Rng2.Value & " + " & Replace(Left(FryGirl3, Len(FryGirl3) -3), "(a)", "") & "](IAF)"[/COLOR]
[COLOR=#000000]    Else[/COLOR]
[COLOR=#000000]        FryGirl3 = "Y = [" &Replace(Left(FryGirl3, Len(FryGirl3) - 3), "(a)", "") &"](IAF)"[/COLOR]
[COLOR=#000000]    End If[/COLOR]
[COLOR=#000000]End Function[/COLOR]

​

 

[Fluff]

Glad you sorted it out & thanks for the feedback