Translate Formula into Business Language

[krystalhaze]

Hi people

I need a little help breaking down this formula into business language - I get the gist of it, but I need to be able to explain the "rules" used to create the values in this cell (formula exists in cell U1).

=IF(LEN(T1)<=255,T1,MID(MID(T1,1,240),1,FIND("|",SUBSTITUTE(MID(T1,1,240)," ","|",LEN(TRIM(MID(T1,1,240)))-LEN(SUBSTITUTE(MID(T1,1,240)," ","")))))&X1&"....")

I understand the beginning portion: if the length of the text in cell T1 is less than or equal to 255 characters, use that text. The next part tells me that if it's more than 255 characters, use the first 240 characters. Then it gets fuzzy with the finds and substitutes because I'm not very familiar with those formulas...

I appreciate any help you all can provide! Let me know if you need more info to break this down.

Thanks!!

 

[Eric W]

If the length of the data in T1 is less than 256, return the full length of T1. Otherwise, truncate T1 after the last space found in the first 240 characters of T1, then append what's in X1 and "..." to that.

It can probably be shortened a bit, the MID(T1,1,240) can be replaced with LEFT(T1,240)

 

[Gerald Higgins]

Can you give us a sample of the entries that might appear in T1 and X1 please ?

It might be easier for us to work out what is happening here, with this information.

 

[krystalhaze]

Thanks, Eric! I thought the same thing about replacing MID with LEFT... unfortunately, this isn't my spreadsheet, or I'd have gone about the whole thing a little differently.

Thanks again for your help!

 

[krystalhaze]

Can you give us a sample of the entries that might appear in T1 and X1 please ?

It might be easier for us to work out what is happening here, with this information.

T1 contains a metric name (e.g., 45:Taxes)
X1 contains a corresponding code (e.g., CCAR220)

I'm not sure how helpful that is...

 

[Eric W]

You're welcome!

 

[Gerald Higgins]

Well the whole point about this seems to be to deal with examples where the text string is more than 240 characters long.

The example you gave - 45:Taxes - is 8 characters long, so that doesn't really help much at all.

ARE THERE any possible examples where the text string is more than 240 characters long ?
If yes, please provide one.
If no, then I don't think you need this formula.

 

[krystalhaze]

Well the whole point about this seems to be to deal with examples where the text string is more than 240 characters long.

The example you gave - 45:Taxes - is 8 characters long, so that doesn't really help much at all.

ARE THERE any possible examples where the text string is more than 240 characters long ?
If yes, please provide one.
If no, then I don't think you need this formula.

Yeah, there definitely are, but I'm a little hesitant to put it here, because it is proprietary data and I don't want to get into any trouble if someone were to stumble across this forum... unlikely, but still!

This formula was created because the system we're loading the data into has character limitations. So, if you really want to test it out, you could type up some gibberish of 256+ characters and see how it works...

I think Eric's explanation pretty well covers me though. I appreciate your willingness to help regardless!

 

[Gerald Higgins]

If you've got the answer you want, then I'm happy to leave it there !