Excel formula in python code

Hello,

I would like to add in my python code formula from excel file: =CONCAT(MID(SUBSTITUTE(A2;" “;”");{15\16\13\14\11\12\9\10\19\20\17\18};1))

To be more precise, when I put the value into entry field in the code and after clicking button “generate” I would like to have the original value re-organized in following order (as above formula from excel). What is the quickest way to do it?

Thank you in advance for the support,.

It may help us, if we knew the contents of cell: A2

Your application isn’t quite clear from the current wording of your post.

(1). Are you wanting to generate a string of an Excel formula that you can copy and paste into Excel?
(2). Where in the generated formula does the entered value go?

(3). The Excel formula also has some syntax errors:

  • Excel expects commas, not semicolons, between function arguments.
  • The SUBSTITUTE( ) function is finding “” and replacing it with “”. In other words, it’s not doing anything.
  • The curly brackets look suspicious.
  • So does the backslashed series of integers.

If you clarify your intent as listed in (1) and (2) above and fix the first two items in (3), I’ll test your formula in Excel and see about helping you put some Python code together.

1 Like

Ok, so the content of the A2 value will be a random value which looks like: CS12 CS13 CS14 CS 15 CS0 CS1 CS2 CS 3 CS4 CS5 CS6 CS7 CS8 CS9 CS10 CS11 and above formula is supposed to order this in following: CS3 CS2 CS1 CS0 CS5 CS4

It works in excel, however I would like to copy it to python.

  1. No, I got currently workign formula in excel. However, I would like to make python app where after inserting the random values CS12 CS13 CS14 CS 15 CS0 CS1 CS2 CS 3 CS4 CS5 CS6 CS7 CS8 CS9 CS10 CS11 and after clicking button “generate” or something it will reorganize it to CS3 CS2 CS1 CS0 CS5 CS4
  2. Generated value is my final value that I will use out of app :slight_smile:

Thanks for that.
tbh, I can’t get your formula to work, mind, I’m not in fact using Excel, rather LibreOffice Calc, but I’ve yet to come across anything, within reason, that is not x-compatible: maybe this the one.

I can see that your SUBSTITUTE is removing any spaces, but beyond that, it’s very unclear to me.

Maybe {15\16\13\14\11\12\9\10\19\20\17\18} which seems to be a part of MID has some special function is Excel that is not present in LO Calc. I can see no argument for the CONCAT at all, but all of that is off topic for this Forum in any case.

All the best with it; I’m sure that someone with better skills that I, will sort this for you.

Ah. On DiscourseMobile, the line wrapped right at the first pair of quotes in SUBSTITUTE ( ), so I couldn’t see the space. @adisc, if you use the code formatting controls shown HERE and HERE, that will help other people read your code.

=CONCAT(MID(SUBSTITUTE(A2;" “;”");{15\16\13\14\11\12\9\10\19\20\17\18};1))

I will try your Excel formula in Excel.

Nope. Excel expects locale specific separators. In locales where comma is used in float (1,23 vs 1.23) the separator for quite obvious reasons can’t be comma.

2 Likes

I’m glad you mentioned the locale factor, Alvar. I always leave my laptop on the default U.S. setting when I travel internationally, so have never had to deal with the comma decimal delimiter in Excel–only in writing emails and such.

We’ll see how the default U.S. setting affects the formula (and then I’ll probably have to put in the commas).

@adisc, you might have to put the semicolons back in if I forget to do it.

=CONCAT(MID(SUBSTITUTE(A2," “,”"),{15\16\13\14\11\12\9\10\19\20\17\18},1))
The second delimiter has curved quotes. This might be caused by Discourse trying to nest the quotes when you paste the formula as message body text. Maybe this behavior can be prevented by using the CodeBlock control character ‘>’.

I changed all of the semicolons and fixed the curved quotes but am also getting an error in Excel. The error message is only “There is a problem with this formula.” and Excel highlights the array/vector (or whatever that is) in curly brackets. So the curly brackets cause an error, at least on my version of Excel. It might be another locale issue with the ‘\’ delimiter because Excel U.S. expects semicolons in matrix definitions.

With semicolons in the array definition, I get this in Excel:

FORMULA: [B2] '=CONCAT(MID(SUBSTITUTE(A2," ",""),{1;16;13;14;11;12;9;10;19;20;17;18},1))'
CELL A2: 'CS12 CS13 CS14 CS 15 CS0 CS1 CS2 CS 3 CS4 CS5 CS6 CS7 CS8 CS9 CS10 CS11'
CELL B2: 'C5CS14CS0CCS'

The MID() function is MID(text, start_num, num_chars). The array of values iterates the formulas across the condensed [A2] string and pulls a single character from each location in the array (1, 16, 13, etc.). How does this formula change the order of any of the values in [A2]?

@adisc, Please post the value you have in [A2] and the result you have in the cell with the formula.

You are correct about the quote characters – this always happens whenever code is not posted as formatted ‘code’.

Thanks for helping me reverse-engineer this and sort it out, Rob. I love Excel as much as Python, so am happy to explore anything that might deepen my understanding. We definitely need the requested info from the OP to get the next level of insight.

Here’s the breakdown of the formula:

  • SUBSTITUTE() strips the spaces to produce a continuous string of characters (as you mentioned above).
  • MID() pulls out the 1st, 16th, 13th, etc. character from the continuous string of chars.
  • CONCAT() makes the individual array results a single string.
    Without the CONCAT(), the formula produces individual MID() results in a series of cells like this (I added the brackets to show that it’s a string and also to detect spaces):

[C]
[5]
[C]
[S]
[1]
[4]
[C]
[S]
[0]
[C]
[C]
[S]

1 Like

Nice work :+1:

You can use the openpyxl package.
Sample usage:

#test on a dummy file first
#not tested
from openpyxl import load_workbook

workbook = load_workbook(filename="FILE NAME")
sheet = workbook.active

sheet["A2"] = "=FORMULA"

workbook.save(filename="FILE NAME")
2 Likes

This is super-cool, @sandraC! Thanks for sharing it. Now I can interface my two favorite computing and calculating tools!!

Hopefully we’ll soon have an Excel formula that actually does what the OP needs and then we’ll be able to look at using that formula in Python in some way.

So far, it’s not clear if we’re looking for a native Python code solution here that’s equivalent to the Excel formula. Library packages might be more complex than what s/he wants. @adisc, can you clarify what approach you intend to use? (Using the Python stdLib [“Standard Library”] is usually best if it can be done cleanly.)