Forum Discussion
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 source that I'm pulling into Excel look like this: 398^4 I need them to look like this: 398.4
Any ideas?
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
4 Replies
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.
If you are satisfied with my solution, kindly mark it as an "Accepted Solution"
Thank you
Good Luck
Nabil Mourad
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
Great idea! I didn't know you could do that. It's not working in this application, I wonder if it's because it's a live data stream into excel. I'll keep messing with it to see what I can do.
Thanks for your help!
