0

I have an excel file with cells containing several values. I try to input this file into a python code to use the mentioned data, but i have the following error: *TypeError: unsupported operand type(s) for ** or pow(): 'str' and 'int'*.

However, I can't find a single instance of either, and this error surprises me as I only have digits in my file, but the command CTRL+F indicates I do have a "**" in each cell, without being able to see it. This code is already working for other .xlsx .csv files, so I don't think this comes from the code.

If tried to use CTRL+H to replace these "**", but it replaces the whole cell with the new character I use, as if all the digits were referring to those asterisks.

I tried to show the functions, without more success.

I tried the following VBA code:

Sub RemoveAsterisks()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If InStr(cell.Value, "**") > 0 Then
            cell.Value = Replace(cell.Value, "**", "A")
        End If
    Next cell
End Sub

I tried to copy and paste all the values (and the values only) and put it in a new spread sheet. Same issue.

I tried the "=SUBSTITUTE()" function.

I tried to copy and paste a cell into another text editor.

Nothing worked. Thus, I don't know how to read this file...

Can you help me? Thanks in advance.

Dominique
  • 16,450
  • 15
  • 56
  • 112
Alexandr3
  • 1
  • 2
  • 1
    When you say you looked up if there was a "**" in there, did you type `**` in the lookup? Because * is a wildcard for "any character any number of times" so that'd make sense that you found it in every cell (which explains why you trying to replace it with `CTRL+H` would replace the entire cell's value). Are you by any chance in your python code trying to use `**` but haven't taken in account the values as string issue like [in this input question](https://stackoverflow.com/questions/20449427/how-can-i-read-inputs-as-numbers)? – Notus_Panda Jun 21 '23 at 10:40
  • 1
    Replace `cell.Value = Replace(cell.Value, "**", "A")` with `Debug.Print cell.Address & " = " & cell.Formula`, and tell us what you find in the Immediate Window. – z32a7ul Jun 21 '23 at 11:12
  • 2
    Read Python's error message carefully: `** or pow()` - did you also search for `pow`? – z32a7ul Jun 21 '23 at 12:12

1 Answers1

0

What's wrong with the Substitute() function, it works quite well, like in this example (based on formula =SUBSTITUTE(C2,"**","||")):

enter image description here

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • I don't think there's anything wrong with the Substitute function but rather the comprehension of the error OP got. PS: he mentioned the ** not being visible in the cell's value which would make the OP think it's "not working". – Notus_Panda Jun 21 '23 at 12:34