Forum Discussion
Solved
Formatting a number that uses a carrot symbol as a decimal point
Hello all, I'd like to be able to format data that I'm pulling in from an external source. I need to format a number that uses the decimal point instead of the carrot symbol. the numbers from the s...
Hi
You can simply Use the Replace Dialog Box (Home Tab >> Find & Select >> Replace) Shortcut CTRL+H
- In the Find What type ^
- in the Replace With type . (period)
- Hit Replace All
Done
Hope that helps
Nabil Mourad
I hope I am not too late to the party, but adding a column with the SUBSTITUTE formula can also be a quick method to handle symbols.
| A | B | |
| 1 | Original | =SUBSTITUTE($A1,"^",".") |
| 2 | 123^0 | 123.0 |
| 3 | 321^1 | 321.1 |
| 4 | 555^2 | 555.2 |
Also to note: For tricky characters, you can use CHAR to reference the ASCII code for the symbol.
=SUBSTITUTE($A1,CHAR(94),CHAR(46))
For example, I have encountered problems where I had to locate and substitute non-breaking spaces with space, grave accent [ ` ] with an apostrophe, and Paragraph with Carriage Return.