Substitute Function - Excel 2016

[cheaptonersucks]

Hi

I have been asked to remove colons from columns in excel 2016. I have used the substitute function to remove the colons from the data but after the function has been applied, it changed the data to a different decimal number with loads of digits after the decimal point! I just need the colon replaced with a decimal point in the new column, but I can’t remember what I need to do so that I don’t get this long random decimal number!

 

Just wondered if anyone knows how to just replace the colon with a decimal point, without changing the original number to a random long decimal please?

 

Edited March 2, 2022 by cheaptonersucks

[Koldov]

Sorry I'm not going to be a lot of help with the actual answer to your issue, but I think that your problem will come from the format of the cells.

 

Excel might have automatically formatted the original cell as 'time', so there isn't really 'text' or a 'number' as such to replace.

[Koldov]

Individually, you could convert it to text into cell D2:

 

=TEXT(C2,"hh:mm")

 

Then have your substitute in E2:

 

=SUBSTITUTE(D2,":",".",1)

 

 

I'm sure someone will come along soon with something more eloquent and joined together in one sum to rule them all...

[asteer]

The format of the orgional data is probably being taken by excel to be a time format (excel probably thinks that the 15:08 is actual 8 minutes past 3pm). All times are actually stored as a decimal number within excel so when you are doing the substitute its converting it back to a decimal number

 

You can change the format of column C to be text (but this will bugger the format up), so you will need a formula like the below

 

=CONCATENATE(LEFT(TEXT(C3,"hh:mm"),2),".",RIGHT(TEXT(C3,"hh:mm"),2))

 

This converts the cell value to text then concatenates the two elements up with a full stop in the middle

[Koldov]

The format of the orgional data is probably being taken by excel to be a time format (excel probably thinks that the 15:08 is actual 8 minutes past 3pm). All times are actually stored as a decimal number within excel so when you are doing the substitute its converting it back to a decimal number

 

You can change the format of column C to be text (but this will bugger the format up), so you will need a formula like the below

 

=CONCATENATE(LEFT(TEXT(C3,"hh:mm"),2),".",RIGHT(TEXT(C3,"hh:mm"),2))

 

This converts the cell value to text then concatenates the two elements up with a full stop in the middle

 

Yes, what he said....

 

Just to note, the forum formatting messed up the formula and put a blank space in the 2nd TEXT command:

 

=CONCATENATE(LEFT(TEXT(C2,"hh:mm"),2),".",RIGHT(TEXT(C2,"hh:mm"),2))

 

You can drag this down, just don't forget to copy and paste as values afterwards.

[cheaptonersucks]

Thanks, I thought that Excel might be interpreting the data as time, due to all of the colons! I have right clicked on the random decimal number and went into format cell. Then tried changing the format to all of the different options such as general or text, but the very long random decimal number still exists!!! Edited March 2, 2022 by cheaptonersucks

[Koldov]

Yes, that is to be expected because as @asteer says Excel stores time as a decimal.

 

You can prove this by typing in the decimal number (0.630555556) in a blank cell and changing the format of the cell to 'Time', it will change to 15:08.

 

 

Also you can verify this by right-clicking and using the format dialogue to show you what will be returned in whatever format you choose in the drop down box.

 

 

Therefore the cell contents will always be either the decimal number or the time (or something else), depending of the format of the cell.

 

But you can change it to text and output the required format using the formula provided.

Edited March 2, 2022 by Koldov

[IRL]

Found a VB script that changes the colon to a period.

 

Sub Changing_Times()
Dim c As Range
For Each c In Selection    

If Hour(c.Value) > 0 Then        
c.NumberFormat = "h.mm"    
Else        
c.NumberFormat = "m.ss"    
End If
Next 
End Sub

Highlight your numbers and run the code.

Edited March 2, 2022 by IRL

[cheaptonersucks]

Thanks I will give the formula a go. When you look online how to implement the substitute function it doesn’t mention anything about how it interprets colons!

[asteer]

Its not the colon itsself that causes the issue, but how excel is interpreting the entire element that your wanting to use the substitute on. Using your substitute formula on test:test to get test.test would work perfectly fine.

 

The issue is excel thinks that any element that is a two digit number, a colon, then a two digit number is a time. While we as humans can say it looks like a time, but it isnt a time, excel can only say it looks like a time, so it is a time.

[Koldov]

Yes, the thing is, it isn't really about how the 'substitute function' interprets colons.

 

It is more to do with how Excel helpfully interprets the data that is entered into it.

 

Once Excel has decided that you have entered a time, it changes the cell formatting to accommodate that and make it easier for you...

 

Obviously the unhelpful part is then if you want to manipulate that data using a function designed for changing 'text'.

 

EDIT: Beaten to it... but this is basically the issue.

[Koldov]

You could look at stopping it automatically formatting that workbook, I'm not sure how the data was entered into it, but it might work in future**

 

https://support.microsoft.com/en-us/office/undo-automatic-formatting-in-excel-54eba206-110c-445a-89f1-c4eb67a36bd4

 

**Disclaimer: I haven't tried this so YMMV...

[browolf]

If this is the kind of thing that you would need to do regularly, then this is the kind of thing that python is very good at and you can script the whole process. (dropping columns etc)

 

check out: Read/write Excel files with Python.

Edited March 2, 2022 by browolf

[asteer]

As an aside, the elements of number:number works for values that arent even valid times i.e. 56:15 will return a automatic value of 02/01/1900 08:15:00

 

The number value for a date/time is based on a sequential element from 01/01/1900 which has a number of 1, while 02/01/1900 has a number of 2. Today (02/03/2022) has a number of 44622, so is forty four thousand sixhundred and twenty two days after 01/01/1900, but there is an error with this if you try and calculate dates back.

 

 

There is an known bug in the calculation that dates after 28th Feb 1900 are one day out, this is because the Microsoft date format was designed to be compatable with Lotus 1-2-3, and the Lotus 1-2-3 programmers through that 1900 was a leap year, which it wasent, so 29/02/1900 is number value of 60.

 

So while today (02/03/2022) is shown in excel as 44622, it should actually be 44621

 

When you do a date calucation in excel, all it does is convert the dates to numbers and then calculates it that way and then formats the number as a date i.e. today plus 5 days is 44622+5 = 44627 formatted as a date

Edited March 2, 2022 by asteer

[DaveTheTech]

You can use flash fill to work this out.

[TABLE=width: 128]

[TR]

[TD=class: xl63, width: 64, align: right]15:08[/TD]

[TD=width: 64, align: right]15.08[/TD]

[/TR]

[TR]

[TD=class: xl63, align: right]12:04[/TD]

[TD=align: right]12.04[/TD]

[/TR]

[TR]

[TD=class: xl63, align: right]13:01[/TD]

[TD=align: right]13.01[/TD]

[/TR]

[/TABLE]

 

I typed in 15.08, then selected it and the following rows.

ctrl+e for flash fill and its sorted

[Koldov]

You can use flash fill to work this out.

 

Very clever, that's a great simple, manual solution (and reasonably quick and easy to do)!

 

@cheaptonersucks - Now you can pass this back to admin/teaching staff... it's not even remotely an IT job (not that it ever was), still hope you get points for finding out a solution!

[cheaptonersucks]

Thanks for all of the advice and info, it’s given me some options.

[cheaptonersucks]

You are right that it’s not an IT job but over the years everything with a plug seems to come my way!! ��

[Koldov]

You are right that it’s not an IT job but over the years everything with a plug seems to come my way!! ��

 

This...

 

Posted about this sort of thing yesterday!

 

http://www.edugeek.net/forums/behind-red-door/86953-request-week-439.html#post1942787