[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-27
8
Medium Priority
?
278 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
0
Comment
Question by:Maliki Hassani
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35476834
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35476842
See attached.

Kevin
CMS-Report.xlsx
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 35476856
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 1000 total points
ID: 35476894
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
 

Author Comment

by:Maliki Hassani
ID: 35477854
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
 

Author Comment

by:Maliki Hassani
ID: 35478072
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35478081
Can you tell us which columns you want to fix? Why do you want to replace the semicolons with periods?

Kevin
0
 

Author Comment

by:Maliki Hassani
ID: 35478106
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question