Text longer than 255 characters in Excel cells

JBoogalooJBoogaloo This too shall pass...Alexandria, VA Icrontian

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.

Comments

  • RyanFodderRyanFodder Detroit, MI Icrontian
    edited December 2014

    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.

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

  • RyanFodderRyanFodder Detroit, MI Icrontian

    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
  • JBoogalooJBoogaloo This too shall pass... Alexandria, VA Icrontian

    @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.

  • JBoogalooJBoogaloo This too shall pass... Alexandria, VA Icrontian
    edited December 2014

    @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.

  • TiberiusLazarusTiberiusLazarus Icrontian
    edited December 2014

    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.

  • RyanFodderRyanFodder Detroit, MI Icrontian
    edited December 2014

    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.

  • TushonTushon I'm scared, Coach Alexandria, VA Icrontian

    Can you throw up an example spreadsheet? ;)

  • JBoogalooJBoogaloo This too shall pass... Alexandria, VA Icrontian

    Unfortunately right now I cannot :/

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

  • RyanFodderRyanFodder Detroit, MI Icrontian

    Couldn't think of an easier way.

  • BobbyDigiBobbyDigi ? R U #Hats ! TX Icrontian
    edited December 2014

    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.

    -Digi

  • JBoogalooJBoogaloo This too shall pass... Alexandria, VA Icrontian

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

  • doabarrellrolldoabarrellroll San Jose, CA Icrontian

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

  • TushonTushon I'm scared, Coach Alexandria, VA Icrontian

    Guessing what he is doing is for gov work.

    JBoogaloo
  • JBoogalooJBoogaloo This too shall pass... Alexandria, VA Icrontian
    edited December 2014

    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.

    BobbyDigi
  • NiGHTSNiGHTS San Diego Icrontian
    edited December 2014

    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)))))

  • RyanFodderRyanFodder Detroit, MI Icrontian
    edited December 2014

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

  • RyderRyder Kalamazoo, Mi Icrontian

    That post broke the forum. Thanks @Nights

    JBoogaloo
  • drasnordrasnor Starship Operator Hawthorne, CA Icrontian

    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.

    JBoogalooGHoosdum
Sign In or Register to comment.