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]

 

[Finalfight40]

Is this what you were after? Assuming you have the textjoin function available:

=TEXTJOIN(" + ", TRUE, IF(B1:B3<> "", B1:B3, ""))

This is an array so entered with Ctrl + Shift + Enter.

 

[FryGirl]

No I do not have textjoin nor can I get it.

 

[Finalfight40]

How about this as a UDF?

Function FryGirl(rng As Range)


Dim cell As Range
Dim text As String


For Each cell In rng
    If cell.text <> "" Then
        text = text & cell.text & " + "
    End If
Next cell


If Right(text, 3) = " + " Then
    text = Left(text, Len(text) - 3)
End If


FryGirl = text


End Function

Then entered the formula as:

=fryGirl(B1:B3)

 

[FryGirl]

That works great to return

123.45 + 15.34 + 12.44

but can it take into account the x-values also?

123.45 + 15.34(x1) + 12.44(x2)

 

[Finalfight40]

Out of curiosity is it just going to be those 3 rows or is your actual data longer?

Also do you want the (a) to appear for the first row?

 

[FryGirl]

It could be up to 10 rows and there may not be an a-value at times. It could just be x1 all by itself. 15.34(x1)

Again, there may not be an a-value, but it will always start with 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]

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]
[TR]
[TD]

4​

[/TD]
[TD]

x3​

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

5​

[/TD]
[TD]

x4​

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

6​

[/TD]
[TD]

x5​

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

7​

[/TD]
[TD]

x6​

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

8​

[/TD]
[TD]

x7​

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

9​

[/TD]
[TD]

x8​

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

10​

[/TD]
[TD]

x9​

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

11​

[/TD]
[TD]

x10​

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

 

[Finalfight40]

Ok how does this work for you?

Function FryGirl2(rng As Range, rng2 As Range)


Dim cell As Range
Dim text As String
Dim count As Integer


For Each cell In rng
count = count + 1
    If cell.text <> "" Then
        text = text & cell.text
        If Left(rng2(count), 1) = "x" Then
            text = text & "(" & rng2(count) & ")"
        End If
        text = text & " + "
    End If
Next cell


If Right(text, 3) = " + " Then
    text = Left(text, Len(text) - 3)
End If


FryGirl2 = text


End Function

and formula would be:

=fryGirl2(B1:B3, A1:A3)

 

[FryGirl]

That is simply awesome. Thank you so much for your time.

 

[Finalfight40]

You are welcome

Happy to help