We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel: How to replace ":" to "."

Medium Priority
294 Views
Last Modified: 2012-08-13
Experts,

I am trying to change the format of all my cells that have ":" to "."

Being that it is a value, I can seem to get it to work. It's not an option under the find and replace.

I would like to wriet a VBA script to this.

Any ideas?
 CMS-Report.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You can change the format to hh.nn rather than hh:mm but some of your data is actually text so you'd need to alter the formulas to use SUBSTITUTE or correct the underlying data to actual time values.
CERTIFIED EXPERT
Top Expert 2008

Commented:
See attached.

Kevin
CMS-Report.xlsx
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks everyone..  

With the issue that there are multiple formats, that is why I wanted to replace the ":" to "." only.

I understand the chaning of the format by replacing but that is not exactly what I am looking for.
Any ideas on how to only replace the : to .?

 

Author

Commented:
Okay so if this isn't the best way to do this perhaps my Crystal report is where I need to try and edit.

Anyone know if a to text will work in Crystal?  I want my crystal report to show exactly what my xls spreadsheet shows.  Currently, it only shows only data that is in general number format.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Can you tell us which columns you want to fix? Why do you want to replace the semicolons with periods?

Kevin

Author

Commented:
When I replace the semicolons with periods my crystal report will show it, any other format it doesn't show anything.

If I could get all the data (:) to change to periods I could make other adjustments for the dates to work.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.