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

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
Maliki HassaniAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The way I did it is I opened the Replace dialog, clicked Options to show the Format buttons, set the Find What format to hh:mm and the Replace With format to hh.mm, and clicked Replace All.

You can, of course, use any target format you desire.

Kevin
0
 
Rory ArchibaldCommented:
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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
See attached.

Kevin
CMS-Report.xlsx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
RunriggerConnect With a Mentor Commented:
some of the values appear to be correctly defined in your source file as Date/Time and it is easy for you to change the format from ":" to "." by applying a custom format on the cell of "HH.MM"

More difficult however are the values which are not correctly Date/Time values e.g :55:39 (cell N6), you need to review why the source file value ('H:\Lance''s Files\[NOC Operator Report.xlsm]NOC Agents CMS'!O9) appears to be "something else."

Feed back what the sourec values appear to be and I may be able to help you further.
Dave
0
 
Maliki HassaniAuthor 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 .?

 
0
 
Maliki HassaniAuthor 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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Can you tell us which columns you want to fix? Why do you want to replace the semicolons with periods?

Kevin
0
 
Maliki HassaniAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.