+ Reply to Thread
Results 1 to 8 of 8

Cannot Find or Replace caret characters (Excel 2013)

  1. #1
    Registered User
    Join Date
    12-08-2011
    Location
    Newfoundland, Canada
    MS-Off Ver
    Excel 2013
    Posts
    10

    Cannot Find or Replace caret characters (Excel 2013)

    I've got a worksheet that contains cells with single-digit numbers but some of the numbers are preceded by a caret character (e.g. ^3).

    There are hundreds of these.

    I know I can use a helper column and formula to pull them out...

    But why doesn't Find & Replace work with ^?

    I've tried ~^ and ALT 94 but they don't work either.

    What am I missing?

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Cannot Find or Replace caret characters (Excel 2013)

    Hi -

    You could use the following in an adjacent column:

    =SUBSTITUTE(F17,"^","Hi")

    Obviously you can substitute whatever text you want instead of "Hi".
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: Cannot Find or Replace caret characters (Excel 2013)

    Are these cells centered and does the ^ appear in the text on the worksheet or just in the formula bar?

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,422

    Re: Cannot Find or Replace caret characters (Excel 2013)

    Create a formula that looks like

    =CODE(LEFT(A1,1))

    where A1 is any cell containing the "^" character and it will tell you the ASCII code for that character, which you can use an ALT sequence to enter in the Find box.

    You can also do an F2 on one of those cells, copy that character, then try pasting it into the Find box. That might work if it's some oddball character that is not really the "SHIFT+6" character.

    If neither of those helps then attach your file. You can even delete all of the data except for one cell with the "^".

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,422

    Re: Cannot Find or Replace caret characters (Excel 2013)

    I should add that in some systems the "^" is used to indicate a special character. That is, ^3 is just a single character. In that case you can't remove the "^". But I have never seen that in Excel.

  6. #6
    Registered User
    Join Date
    12-08-2011
    Location
    Newfoundland, Canada
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Cannot Find or Replace caret characters (Excel 2013)

    Weird! I've never seen this before.

    This spreadsheet came from someone in India. Don't know if that makes a difference.

    The column of data is centered. Some cells have just numbers, some have a caret and a number (e.g. ^3) in both the Formula Bar and in-cell editing but not displayed in the cell.

    When I changed the horizontal alignment to General, the carets were replaced by apostrophes.

    Then I used Text to Columns to re-evaluate the column of numbers and got rid of the apostrophes.

    Maybe this data (with carets) was copied from some other application.

    Thanks for the idea!!

  7. #7
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: Cannot Find or Replace caret characters (Excel 2013)

    If you tried to Find/Replace and they're still there then these are formatting marks added, and used, by Excel. The ^ is centered, ' is left aligned (Can't remember what right aligned is).

    I can't remember why they are added but they should not affect anything and are ignored in formulas and if you export to a CSV.

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,422

    Re: Cannot Find or Replace caret characters (Excel 2013)

    The ' forces the field to be interpreted as text even if it contains numbers. I agree that it probably was imported from another app.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to find and replace from a table in excel 2013
    By Nirmall in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2016, 01:44 PM
  2. [SOLVED] Excel 2013 - VBA Find and Replace
    By colbyski90 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2014, 09:51 AM
  3. Replies: 2
    Last Post: 03-21-2014, 11:32 AM
  4. [SOLVED] find and replace to not replace characters found as wildcards
    By sabutler4 in forum Excel General
    Replies: 4
    Last Post: 07-03-2013, 06:48 PM
  5. [SOLVED] How to find and replace in Excel for special characters?
    By Jill in forum Excel General
    Replies: 1
    Last Post: 05-25-2006, 11:29 AM
  6. Replies: 1
    Last Post: 03-28-2006, 04:40 PM
  7. [SOLVED] Find/Replace carriage return & line feed characters in Excel.
    By Mary Cullen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-04-2005, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1