Shortening a Long Formula

Microsoft Excel for mac 2011 - macintosh
March 22, 2017 at 14:15:42
Specs: Mac OSX 10.8.4
This probably seems quite silly to most of the programmers out there. I'd like to find out
how to shorten a long formula when it repeats a certain part many times within the
formula.

Example: I would like to increase the number by 1 in "The Curious Case (12)" to (13)

I would usually break down the calculation one step at a time. I would find out the location
of both of the brackets, the number of character inside the brackets, the actual value
inside the brackets, then add 1 to that value.

After I have all the formula figured out and instead of having all these columns in between
Col. A and F (the final result), I would replace all the cell references from Col. B - E. So I
would end up with only 2 columns. But now I have a very long and tedious formula
in the new Col. B (former Col. F). How can I shorten this long formula into a much shorter
and sweeter formula that will produce the same result? I hope I got the spacing okay for
viewing. TIA.


Col A	           Col B           Col C            Col D       Col E             Col F
Input              Location of ")" Location of "("  # of Ch.    Content	          Result/Formula

Curious Case (12)  17	           14	            2	        12	          Curious Case (13)
Curious Case (12)  =FIND(")",$A4)  =FIND("(",$A4)   =B4-C4+1    =MID(A4,C4+1,D4)  =REPLACE(A4,C4+1,D4,E4+1)

					
AFTER SUBSTITUTION (with only Col A & B) :
Curious Case (12)  =REPLACE(A4,FIND("(",$A4)+1,FIND(")",$A4)-FIND("(",$A4)-1,MID(A4,FIND("(",$A4)+1,FIND(")",$A4)-FIND("(",$A4)-1)+1)


See More: Shortening a Long Formula

Report •

#1
March 24, 2017 at 05:51:53
It is not clear to me what your ultimate goal is. Are you trying to increase the number inside the parentheses multiple times by dragging the formula down? There is certainly an easier way to do that than what you are doing.

If, on the other hand, you have a bunch of random text strings, with numerical values in different places and you are trying to add a different value to each number (in other words, no consistent pattern) then a long "search and replace" may be the only solution.

Keep in mind that although Excel does provide many functions for dealing with text, it is a "calculation tool" for the most part. Once you start dealing with numerical values embedded in text strings, things can get cumbersome very quickly.

Let us know what you are really trying to do and we'll see what help we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 24, 2017 at 12:11:09
First of all, to answer your question. Yes, I was trying to increase the number inside the parentheses by 1. But please DO NOT pay attention to that. It was shown only to show how I sometimes try to solve a particular problem.

In the example I used here it shows how I broke it down one step at a time:
1. Col B - To find location of ")"
2. Col C - To find location of "("
3. Col D - To find number of characters within the brackets.
4. Col E - To get the content inside the brackets.
5. Col F - Combine the results from Col B - Col E to create a formula that solves the problem.
6. Substitute all references from Col B - Col E to only "Col A" in the formula in Col F before placing it in Col B (shown on the last line).
7. Now I will only have input under Col A, and the result in Col B.

My whole point is not actually dealing with this particular task shown in the example. I could be dealing with something totally different. This has to do with how I usually analyze a problem, break it down to the smallest components. Step by step to create individual formula before joining them all together to have one final formula to produce the result I was looking for. I do believe there are probably many commands (functions) that will get the job done a lot faster and easier.

My main question is: When you look at the final formula in Col. B (on the last line), things got repeated over and over again. Is there a way to make that shorter, avoid repeating some of the variables that many times? Is it possible to create a constant or something like that? Hopefully I've explained what I am trying to achieve here. TIA.


Report •

#3
March 24, 2017 at 14:36:38
The reason parts get repeated "over and over" is because you above to keep finding the same characters in order to find the specific character you are looking for in each case. I don't know if there is a shorter way, I'd have to play around, but I'm not going to be near Excel for a few days.

The reason I asked about incrementing the number is because I would do it this way:

In A4 I would enter ="Curious Case ("&ROW()+8&")" which would resolve to Curious Case (12). Then when I drag it down, the ROW() function would increment by 1 each time.

That may not be what you are looking for here...just something to keep in your back pocket.

Of course, a User Defined Function, written in VBA, would allow you to enter a really short formula in the cell, but you'd first have to write the UDF. The UDF macro might be just as long or longer than your formula, but it would much easier to write.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 24, 2017 at 22:52:54
Thanks for your suggestion to my problem. First of all, like I said, I wasn't really looking for a way to perform such task. I'm only looking for a way to shorten a long formula such as the example when some variables repeat so many times.

As for this particular example, what I usually need is NOT to drag down to number of rows to change the increment. I usually have between 50-100 folders with all different names and a number in bracket behind it. I need to increment the number in bracket by 1. So instead of going to each folder name and manually change them to whatever they are +1. I would copy all the folder names, paste them onto a spreadsheet under Col A, place my formula (that long and silly formula) in Col B before dragging down to as many rows as needed depending on the number of entries in Col A. I would also indicate the location of these folder under Col C before saving it as a tab delimited text file. I would then run an app that will change the name of all the folders (the actual folder) to whatever +1 instantly. Done!


Report •

Ask Question