ms excel find and replace @ symbol results in broken formula

Posted by Loopo on Super User See other posts from Super User or by Loopo
Published on 2013-11-01T16:42:45Z Indexed on 2013/11/03 10:01 UTC
Read the original article Hit count: 343

Filed under:

microsoft-excel

|

find-and-replace

|

function

I'm trying to search and replace in excel, the column is formatted as 'Text'.

Find: [@

replace with: @

Whenever this finds a match at the start of a cell i.e the cell contents start with [@ and tries to replace that with @ the result is an error 'This function is not valid'

I guess that since the @ operator is for references, this is causing the cell to be interpreted differently (not as text anymore)

How do I make this replacement work?

Copy/paste into another program is not a good option because some of the cells contain line-breaks.

© Super User or respective owner

Related posts about microsoft-excel

Connecting to Microsoft Excel using Oracle Data Integrator

as seen on Oracle Blogs - Search for 'Oracle Blogs'
The posts in this series assume that you have some level of familiarity with ODI. The concepts of Topology, Data Server, Physical and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial… >>> More
Connect Microsoft Excel To SQL Azure Database

as seen on Dot net Slackers - Search for 'Dot net Slackers'
A number of people have found my post about getting started with SQL Azure pretty useful. But, it's all worthless if it doesn't add up to user value. Database are like potential energy in physics-it's a promise that something could be put in motion. Users actually making decisions based on analysis… >>> More
How to code Microsoft Excel "Shift Cells Up" feature in SQL

as seen on Stack Overflow - Search for 'Stack Overflow'
Take a simple table like below: Column Headings: || Agent's Name || Time Logged In || Center || Row 1: Andrew || 12:30 PM || Home Base Row 2: Jeff || 7:00 AM || Virtual Base Row 3: Ryan || 6:30 PM || Test Base Now lets say that a single cell is deleted so the table now looks like this: Column… >>> More
a question on using Microsoft Excel to do data analysis

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello everyone there, Me and my mates have been given an Excel spreasheet,which contains the data of the Census report. The column here corresponds to "Year" (from 2000-2010),the row means "City/Town",the cell is simply the population of a town at a particular year. We are up to doing some analysis… >>> More
Working with PivotTables in Excel

as seen on How to geek - Search for 'How to geek'
PivotTables are one of the most powerful features of Microsoft Excel. They allow large amounts of data to be analyzed and summarized in just a few mouse clicks. In this article, we explore PivotTables, understand what they are, and learn how to create and customize them. Note: This article… >>> More

Related posts about find-and-replace

How do I find and replace 'whole words only', in Microsoft Word?

as seen on Super User - Search for 'Super User'
I want to be able to find and replace eat with Eat in Microsoft Word 2007. However, I don’t want to replace every occurrence of eat such as those that can be found inside other words such as great and meat. I have tried doing this using the regular Find and Replace tool, but I get words with capital… >>> More
Find and Replace several several different values all at once

as seen on Super User - Search for 'Super User'
I have a file with multiple instances of Text_1 and Text1 and I need to replace both those strings with Text_A and TextB respectively. Currently I'm doing two Find and Replace functions on each file one that finds Text_1 and replaces it with Text_A and the other that finds Text1 and replaces it… >>> More
Find and replace in a block of text.

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a database full of text fields that look like this: (paragraph of normal text) image:blog/clownboy.jpg (another paragraph) I'm trying to write a view helper for Rails that will take one of these big blocks of text, find bits like "image:blog/clownboy.jpg" and replace them with the corresponding… >>> More
Java List with Objects - find and replace (delete) entry if Object with certain attribute already ex

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi there I've been working all day and I somehow can't get this probably easy task figured out - probably a lack of coffee... I have a synchronizedList where some Objects are being stored. Those objects have a field which is something like an ID. These objects carry information about a user and his… >>> More
Find and replace string in MySQL using data from another table

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, sorry for formatting this wonky but hope you can understand it. I have two MySql tables, and I want to find and replace text strings in one using data in another. Texts - one column: messages 'thx guys' 'i think u r great' 'thx again' ' u rock' Dictionary - two columns: bad_spelling, good_spelling 'thx'… >>> More