There are a series of phone numbers in the column, but some are writen as “=+ 55 11 91234-5678”, and google sheets interprets them as functions, instead of seeing them as numbers. Is there any way to remove the initial “=” en masse?
I tried concatenating, which returns an error, and finding and replacing, which returns nothing.
1
If you are open to using a separate column, you could try using formulatext:
=iferror(substitute(B2,"=",""),substitute(formulatext(B2),"=",""))
where B5 and B6 in my test data are formatted as text.
Removing Equal sign “=” inside the Cell
I have two ways to remove the equal sign from the output. For each cell, you need to add a single quote ' before the equal = sign.
First is for the single cell.
Formula used.
=IF(LEFT(A1, 2) = "=+", "+" & MID(A1, 3, LEN(A1)-2), A1)
Sample Output.
Second is for entire column.
Formula used.
=ARRAYFORMULA(IF(LEFT(A1:A, 2) = "=+", "+" & MID(A1:A, 3, LEN(A1:A)-2), A1:A))
Sample Output.
Note: You need to input the ' at the beginning of the Cell
Input value is on the Column A
Reference.
- IF
- Left
- Mid
- ArrayFormula