Formula cannot go above 255 chars. Help naming variable?

[nobodyukno]

Ok so my formula simply wants to substitute a really long string of text with a blank cell. Problem is that the really long string of text goes over the 255 char limit for a formula.

How can I go about solving this problem. I think you could name the string of text as a variable such as "long text" and then use the substitute formula as such:

=SUBSTITUTE(A1,"long text", "")

Something like that? I don't know if it would work or the steps to name the variable.

[Jonmo1]

Perhaps put the "long text" in another cell, say B1

=SUBSTITUTE(A1,B1,"")

[nobodyukno]

Perhaps put the "long text" in another cell, say B1

=SUBSTITUTE(A1,B1,"")

Ok so what I'm doing is pulling web data and then using formulas to edit the text returned. The web data will vary with each refresh so the "long text" will not always be in the same cell.

[Jonmo1]

I don't understand...

I thought the only problem with this formula
=SUBSTITUTE(A1,"long text", "")
Was because the "long text" couldn't be longer than 255 characters (or the whole formula itself)

How would you have used that formula if there wasn't a limit of 255 characters?

[TMS]

You could define a Named Range, for example, nrLotsOfXXXs containing, say, ="xxxxx...xxxxx".

You could then use, for example,

Please Login or Register  to view this content.

Note that the Named Range is still limited to 255 characters but you can concatenate the Named Ranges together.

So, you could take your long text and split it into two or more Named Ranges and then concatenate them together. Or you might get away with just putting the values in two or more cells and concatenating the values.

Regards, TMS

[nobodyukno]

You could define a Named Range, for example, nrLotsOfXXXs containing, say, ="xxxxx...xxxxx".

You could then use, for example,

Please Login or Register  to view this content.

Note that the Named Range is still limited to 255 characters but you can concatenate the Named Ranges together.

So, you could take your long text and split it into two or more Named Ranges and then concatenate them together. Or you might get away with just putting the values in two or more cells and concatenating the values.

Regards, TMS

Got it to work. Thanks a lot for the explanation!

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.