#1
|
|||
|
|||
Add _ based on a calculated colums
So I am creating new usernames for users in Excel. My format will look be AL11smitJ. The AL is the state the user resides in, the 11 is the Office location number, then I take the first four characters of the last name, and the first character of the first name. Here is the function I use: =LOWER(CONCATENATE(J75,RIGHT(E75,2),LEFT(C75,4),LE FT(A75,1))) My problem is that I need to lengthen last names that are less than four characters with underscores. I have a column that calculates the number of underscores needed to make the last name four characters long, but have no idea were to go from there. |
#2
|
||||
|
||||
Don't know if it works on mac but you will get the idea
Code:
=LOWER(CONCATENATE(J75,RIGHT(E75,2),LEFT(C75,MIN(4,LEN(C75)),REPT("_",4-LEN(C75)),LEFT(A75,1)))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
It looks like this taps out the max number of arguments Excel 2011 can handle. However, I see how you added the underscores. I will post the winning function when I get it done.
|
#4
|
||||
|
||||
Maybe you can replace concatenate with the "&"
=lower(J75&right(.....)&.... ( if it works on MAC)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Got it!! Since I don't care what the spreadsheet looks like when I am done I used some helper columns. Thanks Peco for the help, your REPT suggestion was exactly what I was looking for. I just applied it differently.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Work calculated differently for two seemingly identical tasks | SteveD | Project | 0 | 10-12-2011 02:05 PM |
Calculated Field is disabled | MariaAdc | Excel | 1 | 03-01-2011 02:44 AM |
Calculated dates. | Ziggy-R | Word | 10 | 09-28-2010 01:42 AM |
multiple colums under a single column | RobotChicken | Excel | 3 | 01-06-2010 09:17 AM |
Using calculated field - WHY IS IT SO COMPLICATED? | Riorin | Word | 0 | 10-30-2009 12:20 PM |