#Excel

[ostolero]

learned a new fuckening excel formula

after doing this kind of work for 10 years professionally

it's something incredibly obvious but I was doing it with writing out formulas because I'm too smart for my own good

background:

"upper()" and "lower()" are used on text strings to change the case of the letters. so for example:

upper("apple") = APPLE

lower("Fuck You") = fuck you

and so...

sometimes I would want to make a name like

diane mononym

or

DIANE MONONYM

into

Diane Mononym

and so I would use left() and right() and mid() to pull that shit out carefully and re-assemble it together. I was going to keep it brief because it's a bunch of bullshit but I decided to sit down and write out the formula because the people ought to see bullshit if they really wanted.

below the jump.

before we dive in, let me explain some of the formulas here for your reference.

some stuff with strings:

upper([string]) make the string upper case lower([string]) make the string lowercase concatenate([string) put the contents together as a single string left([string],[number]) take X-many characters off the left right([string],[number]) take Y-many characters off the right trim([string]) remove extra spaces from the string

and some stuff with math:

len([string]) measures how many characters there are in a string for example: "diane" is five (5) letters long, "mononym" is seven (7) characters long search([desired character],[string]): this is used to find a specific character in a string. so like here we'll look for " " the space character. which will denote how far in number of characters into the string that is so for example in "diane mononym" the space " " is six (6) characters in, where the name would be split in half

and so the strategy is to split the name into two words, then split the words into two parts, the first letter and the rest of the characters

so here we go.

the written-out formulas:

A1 ="diane mononym"

B2 =CONCATENATE(

UPPER(LEFT(TRIM(LEFT($A$1,SEARCH(" ",A1))))),

LOWER(RIGHT(TRIM(LEFT($A$1,SEARCH(" ",A1))),LEN(TRIM(LEFT($A$1,SEARCH(" ",A1))))-1)),

" ",

UPPER(LEFT(TRIM(RIGHT($A$1,LEN($A$1)-SEARCH(" ",$A$1))),1)),LOWER(RIGHT(TRIM(RIGHT($A$1,SEARCH(" ",A1))),

LEN(TRIM(LEFT($A$1,LEN($A$1)-SEARCH(" ",A1))))-1))

)

blink yet?

let me annotate this to explain what's happening here.

here's the formulas, annotated:

A1 ="diane mononym" // I wrote this out

B2 =CONCATENATE( // combine this as one string

UPPER(LEFT(TRIM(LEFT($A$1,SEARCH(" ",A1))))),

// use the space character to find out where the space is and grab the left of the two words, take the first character from the left and make it uppercase

LOWER(RIGHT(TRIM(LEFT($A$1,SEARCH(" ",A1))),LEN(TRIM(LEFT($A$1,SEARCH(" ",A1))))-1)),

" ",

// use the space character to find out where the space is and grab the left of the two words, take the remaining characters from the right and make them lowercase

UPPER(LEFT(TRIM(RIGHT($A$1,LEN($A$1)-SEARCH(" ",$A$1))),1)),LOWER(RIGHT(TRIM(RIGHT($A$1,SEARCH(" ",A1))),

// use the space character to find out where the space is and grab the right of the two words, take the first character from the left and make it uppercase

LEN(TRIM(LEFT($A$1,LEN($A$1)-SEARCH(" ",A1))))-1))

// use the space character to find out where the space is and grab the rightof the two words, take the remaining characters from the right and make them lowercase

)

the laborious result:

A1 = diane mononym

B2 = Diane Mononym

This works pretty nicely and with the individual components broken out, you can even do funky stuff like reformat the name to something like

Mononym, Diane

but astute readers will note a specific limitation of this method. what if your person has three names (a middle, naturally),

you are completely shit out of luck.

may as well start this at 9 AM because this is going to take longer than it has to. I am not going today though because getting here writing this has already taken up the better part of an hour.

and I went searching for this because reformatting a name like "KATHERINE HENNESY TEMPO" into "Katherine Hennessy Tempo" would have devoured my soul

but the trick is to use if we're hypothetically tackling this is to be able to count Z-many additional spaces to break up but it's not dynamic. you need to keep adding formulas for each additional segment. hell on earth so let's not

and that's where I arrived at too. hm maybe I'm not too ambitious for my own good.

and so I found it immediately on a brief and mundane stack overflow page. behold. the new formula I did not know existed before today

PROPER()

which does that automatically.

just makes the first letters of a word............ Proper.

Dynamic Mixed Case.

GUESS I better go Fuck Myself

GUESS I BETTER GO FUCK MYSELF

guess i better go fuck myself

Guess I Better Go Fuck Myself

thank you for reading

edit: 20 min later

[a3poify]

Easter egg for office workers: if you go onto Excel and press ctrl+right, then ctrl+down, you will reach cell XFD1048576. If you put a dot in there, then ctrl+A and fill every cell in black, you can then print 34 million black pages from your office printer and get fired

[thatdisasterauthor]

I need to finally cave and learn how to really use Excel, so does anyone have a recommendation for a good beginner Excel course that is TEXT BASED? I do not want to watch videos. Do not make me watch videos.

Free would be best, but anything under $30 would also be manageable right now.

I'm looking for a bare bones basics course to get started. There's things I've sort of learned how to do on my own over the years, but I'd rather start from scratch since I'm sure there's more efficient ways to do those things that I just don't know about.

[lordchinnychin]

Okay so in my Computer Applications class we learned about conditional formatting in Excel, where you can change the color of a cell by inputting certain values.

We're supposed to use it to model heat gradients in metals, but I found a better application:

FROG ART

[a-titty-ninja]

[theshitpostcalligrapher]

okok i KNOW i promised I'd cool it with the new print designs since I made 8 of 'em in a fuckin week but like

the fact two seperate folks got it commissioned.....

yall want this as a print, right?

[phoenixyfriend]

New OC clones idea

His name is XL. He's average clone size, so people are naturally curious about what the name means. They ask if it's a dirty joke about his dingaling.

It's not. He pulls up some files. He's the quartermaster. He uses Space Excel, and it's not CALLED Excel, but he Excels at it. Hence XL.

His friends in two other battalions are Spread and Sheets. All of them get asked the Is It A Dirty Joke question and ALL respond with The Same Fucking Move (files).

This clone's job is spreadsheets and he LOVES it.

[copperbadge]

One of the things I do for my job every January is update a spreadsheet that contains all of the research my org has funded, and what the researchers went on to do after -- basically "We gave them $50K to study this disease and since then they've earned $2M in grants from other sources to continue that study." Overall we've had a great ROI.

But I took over the sheet two years ago, I didn't build it from scratch, and I had to modify it -- stuff like "separating researcher name into first and last name columns", adding a column for source URLs, a column for brief biographies, etc.

It's always had some formatting issues -- cells wouldn't align properly, or wouldn't wrap, or the row height would be truncated, and I couldn't figure out why.

Until I just took a look at the cell styles and realized that the person who had it before me, who was in finance, had formatted every column regardless of content to be either Currency or Accounting.

I'm gonna have to rebuild this fucker from scratch. I'm gonna have to copy the entire thing to a new sheet, paste without formatting, and reformat everything.

*cracks knuckles* should be fun!

(This is an example of how I am just Like That.)

[capitalism-and-analytics]

[animentality]

[takapuolikuu]

Montako ihmistä tarvitaan excelin mielestä vaihtamaan palovaroittimen paristo?

1. tammikuuta 1990

[daakureisaiko]

[expiredcheese]

[a-titty-ninja]

[garbagebinne]