SOLVED

Excel Formula nth String

%3CLINGO-SUB%20id%3D%22lingo-sub-2700694%22%20slang%3D%22en-US%22%3EExcel%20Formula%20nth%20String%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700694%22%20slang%3D%22en-US%22%3E%3CP%3EHave%20an%20excel%20file%20with%20a%20column%20that%20has%20an%20equation.%20Essentially%2C%20I%20want%20to%20see%20the%20newest%20payment%20(from%20the%20equation)%20in%20another%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWant%20to%20do%20the%20following%20in%20a%20column%20next%20to%20the%20previous%20one%3A%3C%2FP%3E%3CP%3E1.%20Convert%20a%20formula%20to%20text.%3C%2FP%3E%3CP%3E2.%20If%20only%20one%20number%20(maximum%20length%20%23%23%23%2C%23%23%23.%23%23)%20add%20that%20text%20after%20the%20%22%3D%22%20sign.%3C%2FP%3E%3CP%3E3.%20Every%20time%20add%20a%20number%20to%20the%20equation%2C%20populate%20the%20nth%20string%20to%20this%20secondary%20column.%3C%2FP%3E%3CP%3E4.%20Keep%20repeating%20until%20the%20last%20nth%20string%20is%20placed%20into%20the%20equation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttachment%20shows%20what%20trying%20to%20achieve.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3EScott%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2700694%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2701222%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20nth%20String%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2701222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1140260%22%20target%3D%22_blank%22%3E%40Scott1417%3C%2FA%3E%26nbsp%3BNot%20sure%20if%20what%20you%20describe%20is%20really%20what%20you%20want.%20The%20example%20you%20gave%20suggests%20you%20want%20to%20see%20a%20running%20total%20in%20the%20table.%20and%20then%20the%20grand%20total%20and%20the%20latest%20billing%20amount%20at%20the%20top.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20we%20can%20take%20it%20from%20there.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2705022%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20nth%20String%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2705022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BFirst%20Thank%20you%20for%20your%20response.%20Very%20much%20appreciate%20your%20efforts%20and%20expertise.%3CBR%20%2F%3ESecondly%2C%20definitely%20a%20misunderstanding.%20Re-attached%20the%20.xlxs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20B%20has%20a%20simple%20addition%20formula%20in%20each%20row%20record.%20This%20is%20what%20I%20am%20trying%20to%20achieve...if%20a%20new%20monthly%20bill%20comes%20in%2C%20then%20place%20that%20new%20monthly%20bill%20in%20column%20C.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20clients%20will%20have%20no%20payment.%20Others%20like%20Client%2012%20will%20have%2012%20monthly%20bills.%20In%20this%20case%2C%20I%20want%20to%20see%20the%20last%20monthly%20bill%20that%20closes-out%20the%20account.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20make%20more%20sense%20now%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2705706%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20nth%20String%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2705706%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1140260%22%20target%3D%22_blank%22%3E%40Scott1417%3C%2FA%3E%26nbsp%3BNot%20really!%20I%20see%20all%20you%20are%20trying%20to%20achieve%20and%20still%20believe%20that%20column%20B%20merely%20holds%20a%20running%20total%20of%20the%20amounts%20in%20column%20C.%20What%20you%20call%20%22a%20simple%20addition%20formula%22%20is%20what%20I%20could%20call%20a%20major%20abuse%20of%20Excel.%20Is%20there%20any%20reason%2C%20why%20you%20want%20to%20'hard-code'%20all%20the%20preceding%20values%20from%20column%20C%20and%20the%20current%20one%20in%20column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20B2%2C%20you%20could%20use%20either%20of%20the%20two%20following%20formulae%20(and%20copy%20them%20down)%20to%20achieve%20the%20same%20result%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(%24C%242%3AC2)%0A%0Aor%0A%0A%3DSUM(B2%2CC1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712812%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20nth%20String%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BNo.%20New%20approach%20in%20the%20.xlsx%20attachment.%26nbsp%3B%20Column%20B%20has%20the%20equation%20(formula)%20and%20want%20Column%20C%20to%20automatically%20show%20the%20last%20entry%20from%20Column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%3C%2FP%3E%3CP%3EB4%20is%20%3D933.93%2B1396.76%3C%2FP%3E%3CP%3EC4%20should%20automate%20as%20a%20value%20is%20added.%20So%20it%20will%20show%20%241%2C396.76.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Test%20Function%20column%20is%20close%20but%20no%20cigar.%26nbsp%3B%20Not%20even%20sure%20it%20is%20on%20the%20right%20path.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeas%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712941%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20nth%20String%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1140260%22%20target%3D%22_blank%22%3E%40Scott1417%3C%2FA%3E%26nbsp%3BSorry%2C%20you%20have%20lost%20me.%20Have%20no%20clue%20what%20you%20are%20trying%20to%20achieve.%20Perhaps%20someone%20else.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Have an excel file with a column that has an equation. Essentially, I want to see the newest payment (from the equation) in another column. 

 

Want to do the following in a column next to the previous one:

1. Convert a formula to text.

2. If only one number (maximum length ###,###.##) add that text after the "=" sign.

3. Every time add a number to the equation, populate the nth string to this secondary column.

4. Keep repeating until the last nth string is placed into the equation.

 

Attachment shows what trying to achieve. 

 

Thank you for your help.

Scott

7 Replies

@Scott1417 Not sure if what you describe is really what you want. The example you gave suggests you want to see a running total in the table. and then the grand total and the latest billing amount at the top.

 

If so, we can take it from there. See attached.

@Riny_van_Eekelen First Thank you for your response. Very much appreciate your efforts and expertise.
Secondly, definitely a misunderstanding. Re-attached the .xlxs.

 

Column B has a simple addition formula in each row record. This is what I am trying to achieve...if a new monthly bill comes in, then place that new monthly bill in column C.  

 

Some clients will have no payment. Others like Client 12 will have 12 monthly bills. In this case, I want to see the last monthly bill that closes-out the account.

 

Does this make more sense now?

@Scott1417 Not really! I see all you are trying to achieve and still believe that column B merely holds a running total of the amounts in column C. What you call "a simple addition formula" is what I could call a major abuse of Excel. Is there any reason, why you want to 'hard-code' all the preceding values from column C and the current one in column B.

 

In B2, you could use either of the two following formulae (and copy them down) to achieve the same result:

=SUM($C$2:C2)

or

=SUM(B2,C1)

 

@Riny_van_Eekelen No. New approach in the .xlsx attachment.  Column B has the equation (formula) and want Column C to automatically show the last entry from Column B.

 

For example,

B4 is =933.93+1396.76

C4 should automate as a value is added. So it will show $1,396.76.

 

The Test Function column is close but no cigar.  Not even sure it is on the right path.

 

Ideas? 

@Scott1417 Sorry, you have lost me. Have no clue what you are trying to achieve. Perhaps someone else.

best response confirmed by allyreckerman (Microsoft)
Solution

@Scott1417 

You are working backwards.

Everyone else puts the numbers in column B and then puts the formula in C3:

=SUM(C$3:C3)

 

Maybe that is what confuses @Riny_van_Eekelen.

 

Try this:

=--SUBSTITUTE(FILTERXML("<y><z>"&SUBSTITUTE(FORMULATEXT(B3),"+","</z><z>")&"</z></y>","//z[last()]"),"=","")

 

Detlef,

Thank you for your asssistance. The subbstitute fits perfectly. Can report, I have been employing this formula this am. It’s working. When finish my task I’ll report the full outcome next week.

Riny,

Thank you for your assistance and persistence. If not, for you no way we have Detlef chime in with his thoughts.

Guys...we teamed up well here:) Cooperation is a job well done. Awesome work!

Give update next week.
Scott