Concatenate with some additional filters?

[Dave Podschweit]

Hello All,

I have a concatenate question that I hope someone can help with. I am
creating a spreadsheet with users in Excel 2003, and I need to merge
cells together, but I also need some additional filters in place.

here is my function as it is written now:

D2=LOWER(CONCATENATE(LEFT(A2),LEFT(B2),TRIM(C2)))

FirstName MiddleName LastName Username
John Quincy Public jqpublic

The formula works great in this situation, but lets complicate
matters.

I have multiple users with punctuation in their name, and creating
usernames with punctuation=bad.

John Quincy O'Brien-Public, III jqo'brien-public, iii

If I was entering all of the name information manually, this would not
be a problem, but all of that information is exported from a database
and dropped into an excell file for me to convert and then import into
active directory to create user accounts every year at a educational
institution. I obviously would like to do this as programmatically as
humanly possible.

My thoughts are to do replace on the 's and -s, but I don't know how
to replace them with a <backspace> essentially. My thought on the ,s
is to truncate at that point, but I am unsure as to how I should
accomplish this.

Please help,

Thanks,
Dave

 

[Jason Morin]

=LOWER(LEFT(A2)&LEFT(B2)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(C2,CHAR(39),""),CHAR(45),""),CHAR(32),""))

HTH
Jason
Atlanta, GA

 

[Dave Podschweit]

Jason,

Thanks for the help. you did get me part way there. I had to change the
function because it returned an error to this:

D3=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(CONCATENATE(LEFT(A3),LEFT(B3),TRIM
(C3))),CHAR(39),""),CHAR(45),""),CHAR(32),"")

Thank you much for that, but I am still left with the problem of the
comma... I assume that one of the char(32?) is supposed to get a comma. I
can figure that number out, but I also want to drop everything that would
occur after the comma

as an example

John Quincy O'Brien-Public, III would end up generating a username of
jqobrienpublic not jqobrienpubliciii


I know I can use & instead of the concatenate command, but the function
makes more sense to me when I do it this way.

Thanks again,
Dave

 

[Dave Podschweit]

CHAR(44) not CHAR(32) was a comma

=LOWER(LEFT(A2)&LEFT(B2)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(C2,CHAR(39),""),CHAR(45),""),CHAR(32),""))

HTH
Jason
Atlanta, GA

 

[Dave Podschweit]

Thanks to your help I figured it out.

I used the following function

=LEFT(SUBSTITUTE(SUBSTITUTE(LOWER(CONCATENATE(LEFT(A3),LEFT(B3),TRIM(C3))),C
HAR(39),""),CHAR(45),""),((FIND(CHAR(44),(C3))-1)))

Thanks Again,
Dave