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?
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.
Are these cells centered and does the ^ appear in the text on the worksheet or just in the formula bar?
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 "^".
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.
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!!
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.
The ' forces the field to be interpreted as text even if it contains numbers. I agree that it probably was imported from another app.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks