Automatically replace

Hello, I would like to know if it is possible to have an Excel Spreadsheet automatically replace characters that are typed or pasted into a cell. For example, if I type or paste B3:0/0 in a cell, could it automatically make the value B3[0].0 instead? Thank you.

Originally Posted by Sambo kid

if it is a consistent change such as :0/ being replaced by [0]. then you could use find and replace. otherwise you probably need VBA (which I don't have as a skill set).

Thank you for the reply. Yes however, the 0/0 could also be 3/15, for example, the constants vary.

The answer to this depends on what conditions the replacements follow. Providing a sample would greatly help us help you, including examples of your desired output.

It also depends on if you mean as the values are typed in or at some later point. Additionally, why do you need to do this? Is it in an effort to get everyone to enter stuff in the same syntax or something else?

It may be possible to use custom formatting to accomplish this (however that would only handle changing everything to 1 thing/format). If you need it in cell "as its typed" this would require VBA, likely based on a worksheet_change event or something like that. If you are ok with it being displayed as you want in another cell, then a formula could likely do it.

If the reason behind this is to get everyone to enter things in the same syntax, then you likely want data validation to force them to enter it correctly.

So, what I would have is a series of values: B3:0/0, B3:0/10, B3:5/0, B3:120/13......... and I would need to replace them with B3:[0].0, B3:[0].10, B3:[5].0, B3:[120].13......

Originally Posted by porucha vevrku

Are these series already entered in the worksheet cells or will they be only/just entered manually (futurity) ?
Are there schemes like: B3:1/130, B3:12/1, B3:12/13, B3:12/130, B3:120/1, B3:120/130, i.e.: B3:#/###, B3:##/#, B3:##/##, B3:##/###, B3:###/#, B3:###/###, others ?

Edit:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim strToChng As String

    If TypeName(Sh) = "Worksheet" Then
        strToChng = Target.Value
        If strToChng Like "[A-Z]#:#/#" Or _
           strToChng Like "[A-Z]#:#/##" Or _
           strToChng Like "[A-Z]#:#/###" Or _
           strToChng Like "[A-Z]#:##/#" Or _
           strToChng Like "[A-Z]#:##/##" Or _
           strToChng Like "[A-Z]#:##/###" Or _
           strToChng Like "[A-Z]#:###/#" Or _
           strToChng Like "[A-Z]#:###/##" Or _
           strToChng Like "[A-Z]#:###/###" Then
            
            strToChng = Replace(strToChng, ":", ":[", 1, -1, 1)
            strToChng = Replace(strToChng, "/", "].", 1, -1, 1)
            Target.Value = strToChng
        End If
    End If
End Sub

I will either enter them or paste them from elsewhere.

Check if it will work.
The procedure transferred to the module of the given sheet in which the data will be entered/pasted, e.g. Sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strToChng As String
Dim cellTarget As Range

On Error GoTo the_end
    Application.EnableEvents = False
    For Each cellTarget In ActiveSheet.Range(Target.Address)
        strToChng = cellTarget.Value
        If strToChng <> "" Then
            If strToChng Like "[A-Z]#:#/#" Or strToChng Like "[A-Z]#:#/##" Or strToChng Like "[A-Z]#:#/###" Or _
               strToChng Like "[A-Z]#:##/#" Or strToChng Like "[A-Z]#:##/##" Or strToChng Like "[A-Z]#:##/###" Or _
               strToChng Like "[A-Z]#:###/#" Or strToChng Like "[A-Z]#:###/##" Or strToChng Like "[A-Z]#:###/###" Then
                
                strToChng = Replace(strToChng, ":", ":[", 1, -1, 1)
                strToChng = Replace(strToChng, "/", "].", 1, -1, 1)
                cellTarget.Value = strToChng
            End If
        End If
    Next
    
the_end:
    Application.EnableEvents = True
End Sub