Text longer than 255 characters in Excel cells

JBoogaloo

People,

I'm looking online and can't seem to come across what I'm looking for (it's possible it's a bit ridiculous, too) so I need the amazing brains of Icrontic. I'm working on something for work and I'm hoping to get desired results utilizing the IF function. To keep the explanation as simple as I can (I think), let's just say I'm using 4 cells total, 2 of which have more than 255 characters in them, 1 that is a dropdown (with >255chars that contains the same info as one of the populated other cells) and one blank cell.

Let's see if I can explain what I need without hurting your brains with my thought process: If CellA dropdown information matches the same info in CellB, then CellC needs to be populated with information from CellD. Does that make sense? In simpler (maybe?) terms the function would be =IF(B2=D100, A5=A100), but since the 255 character limit gives me this error "Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&)" I'm not sure what I can do.

I'm not really familiar with using the concatenate function and I'm not even sure if I can break one cell into 3 or more and then use the concatenate function within an IF function to get my results. Without needing to code a workaround (because work allows none of that stuff...think all you have is a laptop with office and no other abilities to accomplish this) Is there an simpler way use the IF function with cells greater than 255 characters and do you have any recommendations for my problem above?

Thanks in advance for anything and sorry if I just gave you the "WTF is he talking about" face.

Also, I don't have my fire resist gear on today so be gentle.

RyanFodder

Are you familiar with the vlookup command? It sounds more useful than you IF statement. I've never used it on that long of text though, testing now.

Edit: I was able to make your formula work with a huge string of characters. What version of Excel are you using?

Edit2: I have time to do a screen share (skype, google desktop?, email share) if you're interested.

TiberiusLazarus

I'd love to help but I'm having trouble understanding :/

RyanFodder

My understanding:

CellB2_i'm_a_long_text_string | CellD100_i_might_be_the same text string
CellA5: Formula goes here | Cell A100: value to go into CellA5 if long text strings match

JBoogaloo

@CannonFodder said:
Are you familiar with the vlookup command? It sounds more useful than you IF statement. I've never used it on that long of text though, testing now.

Edit: I was able to make your formula work with a huge string of characters. What version of Excel are you using?

Edit2: I have time to do a screen share (skype, google desktop?, email share) if you're interested.

You're awesome! I don't get home for a couple hours, but if you had time to Skype later that'd be super helpful.

@TiberiusLazarus‌ totally understandable, I have a hard time understanding my own brain at times.

JBoogaloo

@CannonFodder said:
My understanding:

CellB2_i'm_a_long_text_string | CellD100_i_might_be_the same text string
CellA5: Formula goes here | Cell A100: value to go into CellA5 if long text strings match

Yes!

Edit: Also, CellA100 is ridiculous long string of text, too.

TiberiusLazarus

So... in cell A5 put =If(B2=D100,A100) ?

Edit: This gets harder if you need to make the cell references dynamic. Doable, just not sure where the values come from.

RyanFodder

the formula I used was slightly different:

=if(B2=D100,A100,"Cells don't match, or generic error statement")

Vlookup/Hlookup won't work for you, I think. Edit: depends on how your spreadsheet is laid out.

Tushon

Can you throw up an example spreadsheet?

JBoogaloo

Unfortunately right now I cannot :/

RyanFodder

https://www.dropbox.com/s/inakbwsqr1sfvuy/Book3.xlsx?dl=0

TiberiusLazarus

Holy crap excel files are awful on dropbox. But it gets the job done

RyanFodder

Couldn't think of an easier way.

BobbyDigi

I love "coding" in Excel so if CannonFodder's example doesn't work out (it looks sound), ping me and we'll get together for a brainstorm.

JBoogaloo

Thanks guys. On my way home and I'll look at this after I fight the good fight against traffic.

doabarrellroll

Or, you could simply use Google Sheets. Make the cell contents as long as you well please, for free!

Tushon

Guessing what he is doing is for gov work.

JBoogaloo

Oh, if I could I certainly would, but where I work doesn't allow that...doesn't allow much of anything really.

BUUUUUUUUUUT, because @CannonFodder‌'s awesome, the formula worked. You guys' brains is so great, now I'm smarter for knowing. Thanks dude and thanks, everyone!

Edit: @Tushon is right.

NiGHTS

Keep coming back for Excel help, always fun to teach someone how to use the program to their benefit.

My useless superpower is writing incredibly inefficient excel formulas for no other reason than to amuse myself and the next poor soul that must use my spreadsheet. This is basically an =average() formula:

=IF($J$9<OFFSET(J20,24,0),$J$9/OFFSET(J20,24,0)*OFFSET(F20,24,0),IF($J$9<(OFFSET(J20,24,0)+OFFSET(J20,22,0)),OFFSET(F20,24,0)+($J$9-OFFSET(J20,24,0))/OFFSET(J20,22,0)*OFFSET(F20,22,0),IF($J$9<(OFFSET(J20,24,0)+OFFSET(J20,22,0)+OFFSET(J20,20,0)),OFFSET(F20,24,0)+OFFSET(F20,22,0)+($J$9-OFFSET(J20,24,0)-OFFSET(J20,22,0))/OFFSET(J20,20,0)*OFFSET(F20,20,0),IF($J$9<(OFFSET(J20,24,0)+OFFSET(J20,22,0)+OFFSET(J20,20,0)+OFFSET(J20,18,0)),OFFSET(F20,24,0)+OFFSET(F20,22,0)+OFFSET(F20,20,0)+($J$9-OFFSET(J20,24,0)-OFFSET(J20,22,0)-OFFSET(J20,20,0))/OFFSET(J20,18,0)*OFFSET(F20,18,0),OFFSET(F20,24,0)+OFFSET(F20,22,0)+OFFSET(F20,20,0)+OFFSET(J20,18,0)+OFFSET(J20,16,0)))))

RyanFodder

That... kills... the cra(b)

Ryder

That post broke the forum. Thanks @Nights

drasnor

Most times that I encounter these kinds of problems, I re-evaluate my life decisions and then write real software to solve the problem instead of trying to shoehorn in a spreadsheet.