Join Text with if conditions returning two different values

[Masks44]

Basically I'm joining text but using and concatenate LEFT with it but including a cell that may be blank or not blank but it's not working correctly if the cell is blank
=IF(ISBLANK(G3),UPPER(CONCATENATE(LEFT($F3,1),LEFT($H3,7))),UPPER(CONCATENATE(LEFT($H3,1),$G3,LEFT($F3,6))))

If tried =IF(ISBLANK(G3),UPPER(CONCATENATE(LEFT($H3,1),LEFT($F3,7))),(IF(ISTEXT(G3),UPPER(CONCATENATE(LEFT($H3,1),$G3,LEFT($F4,6))))
=IF(ISTEXT(G3),UPPER(CONCATENATE(LEFT($H3,2),$G3,LEFT($F3,5))),UPPER(CONCATENATE(LEFT($H3,1),LEFT($F3,7))))

Think lastname, first name middle initial - but when I'm wanting to join it to make a username of 8 characters total but use middle initial if there is one but still get 8 characters if there isn't a middle initial. I end up with 7 characters only if no middle initial.
I've tried hidden columns and tried to use if statement for true or false but I'm always ending up with 7 characters when no middle initial K and L have the correct value but still results only 7
=IF(ISBLANK(G3),L3,K3)
=IF(ISBLANK(G3),L3),(IF(ISTEXT(G2),K3))
but doesn't return the value of L3 which is correct for the 8 characters it only gives me 7 yet the formula in that cell gives me the correct but it then gives me only 7.. uuurrrr


but still end up with 7 characters when there is no middle initial uuurrrggggg

I've tried the new TEXTJOIN and CONCAT get same results. I know there is a way to do this, I'm just drawing a blank at this moment.

Driving me crazy.

[Rick Rothstein]

Can you give us a few samples of data you may have and show us what you want from it?

[AliGW]

Please update your forum profile - TEXTJOIN and CONCAT are in Excel 2019 and later - which do you have?

Please provude a sample workbook with several rows of data showing all eventualities and the outcomes you expect.

There are inconsistencies in your formulae. What is the logic for the concatenation?

[Masks44]

Office 365 Excel 2016 version
If there is no middle initial I'm always short characters when I want 8 characters.
Example.JPG

[JeteMc]

Try the following in cells I2 and down: =IF(G2="",UPPER(CONCATENATE(LEFT($F2,1),LEFT($H2,7))),UPPER(CONCATENATE(LEFT($H2,1),$G2,LEFT($F2,6))))
If that doesn't work then please utilize the information in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page to attach the Excel file from which the screenshot in post #4 is taken.
Let us know if you have any questions.

[Gregb11]

Office 365 Excel 2016 version

These are 2 different versions. Which is it.

You don't really need CONCATENATE. Try this:

=IF(G2="",UPPER(LEFT($H2,1)&LEFT($F2,7)),UPPER(LEFT($H2,1)&$G2&LEFT($F2,6)))

You don't say what you want to have happen if the last name is less than 7 characters, but I assume you have that handled

[AliGW]

Office 365 Excel 2016 version

You haven't updated your profile - please change it to Office 365. Thanks.

I asked for a sample workbook, NOT a picture! Please attach a workbook.

[Masks44]

Thank you JeteMc that did the trick. All good now :-)

[Masks44]

Thank you that worked

Going across the columns. Worked perfectly. Thank you!!!!

=IF(G3="",UPPER(CONCATENATE(LEFT($H3,2),LEFT($F3,6))),UPPER(CONCATENATE(LEFT($H3,1),$G3,LEFT($F3,6))))

=IF(G3="",UPPER(CONCATENATE(LEFT($H3,3),LEFT($F3,5))),UPPER(CONCATENATE(LEFT($H3,2),$G3,LEFT($F3,5))))

=IF(G3="",UPPER(CONCATENATE(LEFT($H3,4),LEFT($F3,4))),UPPER(CONCATENATE(LEFT($H3,4),$G3,LEFT($F3,3))))

=IF(G3="",UPPER(CONCATENATE(LEFT($H3,5),LEFT($F3,3))),UPPER(CONCATENATE(LEFT($H3,5),$G3,LEFT($F3,2))))

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[AliGW]

@Masks44

Please update your forum profile as requested earlier. Thanks.