Member Avatar for mattyd

(Mod: if this is not the appropriate area for this post, please move. Thanks.)

I am attempting to Find and Replace text in an Excel 2016 spreadsheet.

For example, I'm searching for the following URL in the spreadsheet:

Upon located this URL in the spreadsheet, I want to replace the following with the @ symbol:

https://sample.com/

The result would be this:

@user

Previously, I was using Excel online and was able to do this easily. I'm now using a desktop version which is giving me the following error message: "This function is invalid"

When I attempt to make the change manually, it seems to initiate a function; I am not familiar with Excel functions and do not need this at this time.

I've read online that the @ symbol is reserved and related to functions (?) and therefore cannot be used as a simple text symbol-- is this true?

Is there any method (or work-around) to achieve this?

Thank you in advance.

  • 2 Contributors
  • 1 Reply
  • 429 Views
  • 31 Minutes Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

Two simple, non-VBA fixes that are reliable in desktop Excel.

One quick trick (no macros): use Replace (Ctrl+H). Put the site root in Find what and put an apostrophe followed by the at sign in Replace with (type '@). The leading apostrophe forces Excel to treat the replacement as plain text, so a cell that was "site-root/username" becomes "'@username" internally and displays as @username. In the Replace dialog open Options and confirm "Within" (Sheet/Workbook) and "Look in" (Values vs Formulas) are set appropriately before hitting Replace All.

A second approach is to convert with a formula, then paste values. For example, if the original URL is in A2 use:

=SUBSTITUTE(A2,"https://sample.com/","@")

Fill down, then Copy → Paste Special → Values over the original column.

Troubleshooting notes and edge cases: if the cells are hyperlinks (not plain text), use Remove Hyperlink first (right-click → Remove Hyperlink) or extract the display text before replacing. If the data is inside a Table, remember that @ has meaning in structured references; either convert the table to a range or use the helper-column method above. Also check for formulas that generate the URL (HYPERLINK or CONCAT); if the URL is inside a formula, set "Look in" to Formulas or use SUBSTITUTE on the formula output cell instead of editing the formula text directly.

is correct that VBA can automate this across complex workbooks, but in most cases the apostrophe Replace or a SUBSTITUTE helper column is simpler and safer.

Member Avatar for rproffitt

2 things. Your blog looks ancient in content.

Second I think you'll have to write some VBA code to do this one. I didn't find the search and replace to do what you asked.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.