Concatenation Question

[reberryjr]

I have what should be a simple task. I'm trying to concatenate some cells to complete a name. I didn't have an issue before I added the middle initial to the formula, but now I'm struggling. I've searched the forum, but only see concatenation examples for First and Last names, not anything with 4 parameters that accounts for missing data (MI and/or Suffix).

The basic layout is below, with expected results. Here's the formula that I'm using, that is not working:

=IF(G3="",IF(E3="",D3&" "&F3,IF(G3="",D3&" "&E3&" "&F3,IF(E3="",D3&" "&F3&" "&G3,D3&" "&E3&" "&F3&" "&G3))))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[/TR]
[TR]
[TD]First[/TD]
[TD]MI[/TD]
[TD]Last[/TD]
[TD]Suffix[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD]J.[/TD]
[TD]Boy[/TD]
[TD]Sr.[/TD]
[TD]Tommy J. Boy Sr.[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD][/TD]
[TD]Boy[/TD]
[TD]Sr.[/TD]
[TD]Tommy Boy Sr.[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD]J.[/TD]
[TD]Boy[/TD]
[TD][/TD]
[TD]Tommy J. Boy[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD][/TD]
[TD]Boy[/TD]
[TD][/TD]
[TD]Tommy Boy[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=TRIM(D3&" "&E3&" "&F3&" "&G3)

 

[reberryjr]

Yikes. I never knew that was possible. I've always used the method in my post. This is so much easier. Time to go review what TRIM actually does. Thank you so much....again! You're 2 for 2!

 

[Fluff]

Glad to help & thanks for the feedback

 

[reberryjr]

I tried to apply this logic to a text box on a userform and I'm not getting any results in the text box. Thoughts?

'Me.txt_Name.Value = Trim(Me.txt_First.Text & Me.txt_MI.Text & Me.txt_Last.Text & Me.txt_Suff.Text)
'Me.txt_Name.Value = Trim(Me.txt_First.Value & Me.txt_MI.Value & Me.txt_Last.Value & Me.txt_Suff.Value)

 

[Fluff]

It works for me.
Are your textboxes named correctly?

 

[reberryjr]

I revalidated that they are named as listed in the code. I'm wanting the txt_Name to actually concatenate the First, MI, Last and Suff; as they're typed in the form. Not sure why neither line of code works.

 

[Fluff]

How are your triggering it?

 

[reberryjr]

Well, I don't know that I'm actually triggering it per se. I tried both lines on the initialization of the form, as well as the code that's tied to a cmd button. In thinking through it now, the code tied to the submit button probably wouldn't take effect until the sub ends, which would be why I don't see the results in txt_Name. I was thinking it would act like a formula in a cell, and respond as text is entered in the other text boxes.

 

[Fluff]

You could do it on a doubleclick event

Private Sub txt_Name_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.txt_Name.Value = Trim(Me.txt_First.Value & Me.txt_MI.Value & Me.txt_Last.Value & Me.txt_Suff.Value)

End Sub

Enter the data in the txtbxs then double click on txt_Name