-1

I need to replace all : (colon) with :  (colon space). I am able to do this with replace function. 

description_string = Replace(currentSheet.Cells(i, j), ":", ": ")

but this replace time formats (eg 16:20) also. I need to identify the colons : which has number as a preceding value (eg. 9:)

i have tried using regular expressions

[a-zA-Z]:[a-zA-Z0-9]

as the pattern. The issue is how will I replace only colon from this. If I try replacing, the entire characters before and after the colon is getting replaced.

I tried find and replace but the issue is for the string like

abcd Date:01/01/17 10:18 Des:xyz

all the colons are getting changed to :

The output I want is

abcd Date: 01/01/17 10:18 Des: xyz

Manu Mohan
  • 167
  • 3
  • 17
  • Do you mean that you don't want to replace if there is a number before the colon? – SJR May 04 '17 at 12:05
  • Look up "using regular expressions in vba" - that will give you want you need to solve the problem. If you are still having trouble once you've found a few examples (there are literally hundreds on [so]) come back and clarify the question or post a new one. – Dave May 04 '17 at 12:07
  • How about using Regular Expresions? [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) – Pᴇʜ May 04 '17 at 12:07
  • @SJR yes. I don't want to replace if there is a number before the colon? – Manu Mohan May 04 '17 at 13:19
  • Can you post the rest of your code? Use regexp as suggested, or do a check of the position of the colon (Instr) and check the preceding character before replacing. – SJR May 04 '17 at 13:31

1 Answers1

2

Try using regular expression pattern 

reg.Pattern = "(\D:)" ' non-digit followed by colon, as group $1

\D specifies a non digit character. (\D:) specifies a non-digit character followed by colon. Parenthesized, this matched pattern will be identified as $1 in the replacement later; because it is the first (and only) captured group.

Then replace like this:

newString = reg.Replace(oldString, "$1 ") 'replace captured group by itself then space

Replaces the matched group ($1) by itself followed by space: "$1 "

An alternative in cases of only one group in the pattern (like this one), you can drop the parentheses and identify the (only) captured group as $+.

reg.Pattern = "\D:" ' non-digit followed by colon, as the only group ($+)
newString = reg.Replace(oldString, "$+ ")

Sub Example()
  Dim reg As New RegExp: reg.Global = True: reg.Pattern = "(\D:)"
  Dim oldString As String: oldString = "abcd Date:01/01/17 10:18 Des:xyz"
  Dim newString As String: newString = reg.Replace(oldString, "$1 ")
  Debug.Print newString
End Sub

abcd Date: 01/01/17 10:18 Des: xyz
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • Thank you so much [A.S.H.](http://stackoverflow.com/users/4926357/a-s-h) . This helped me a lot. Can you please help me understand how the replace function worked in this scenario? – Manu Mohan May 17 '17 at 12:16
  • 1
    @ManuMohan you're welcome. There are some exaplanations as comments in the answer. `\D` specifies a non digit character. `(\D:)` specifies a non-digit character followed by colon. parenthesized means this matched pattern will be identified as `$1` in the replacement. Finally, the replacement replaces the matched group (`$1`) by itself followed by space. Hope this helps. – A.S.H May 17 '17 at 12:27
  • Thanks ASH.. i just got confused by '$1'. So my understanding is $1 will be always the string identified by the regexp. Correct me if I'm wrong. – Manu Mohan May 17 '17 at 12:31
  • 1
    @ManuMohan $1 is the first "parenthesized" group (here it's the only one). An alternative in cases of only one group would be to not use parentheses and identify the (only) captured group as `$+`. `.Pattern = "\D:"` and `reg.Replace(oldString, "$+ ")`. – A.S.H May 17 '17 at 12:33
  • I added these explanations to the answer. – A.S.H May 17 '17 at 12:42