INSERT A SPACE INTO A TEXT VALUE

[JonRowland]

Hi,

Hope someone can help me.

I have a number of text values. However, I need to be able to add a space into these after the fifth character.

Any ideas how I can do this?

Thanks

Jon

 

[colibri]

thank alot mr. hotpepper ur answer working

i want ask again (reverse with my question before)

abc.12.ab how to make it abc12ab
a.8.b.6.f.4 how to make it a8b6f4

thank in advance

 

[Rick Rothstein]

Select the column and/or row of (or simply the) cells with your text, click Edit/Replace and put the dot in the "Find what" field, leave the "Replace with" field empty, then click the "Options>>" button (assuming the options are not already displayed) and make sure the "Match entire cell contents" checkbox is not checked, then click the OK button.

 

[Peter_SSs]

The second one, I would use a UDF:

Function adddots(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "(.)"
    .Global = True
    adddots = .Replace(r, "$1.")
    adddots = Left(adddots, Len(adddots) - 1)
End With
End Function

Just a comment. The second 'adddots = ...' line to trim off the final dot could be dispensed with if the Pattern was changed to
"(.)(?=.)"

 

[Rick Rothstein]

For your question about inserting dots between the digits and letters, I thought you might like to see a non-Regular Expression UDF function (using standard built-in VB commands) which will do that...

Function InsertDots(S As String) As String
  Dim X As Long
  InsertDots = S
  For X = Len(S) To 2 Step -1
    If Mid(S, X, 2) Like "#[A-Za-z]" Or Mid(S, X, 2) Like "[A-Za-z]#" Then InsertDots = Left(InsertDots, X) & "." & Mid(InsertDots, X + 1)
  Next
End Function

As for your question about removing dots, if you want a UDF solution for that, then this should work for you...

Function RemoveDots(S As String) As String
  RemoveDots = Replace(S, ".", "")
End Function

 

[Scott Huish]

And a formula solution:

=SUBSTITUTE(A1,".","")

 

[Scott Huish]

Just a comment. The second 'adddots = ...' line to trim off the final dot could be dispensed with if the Pattern was changed to
"(.)(?=.)"

What's the ?= for?

 

[Peter_SSs]

What's the ?= for?

?= means "look ahead".
So the pattern is "(any character)(followed by any character)".
However the look ahead part does not get returned as part of the match, it just establishes a position for the actual match.

Hence the pattern matches every character in the string - except the last one since that one isn't followed by any character.

Edit:
BTW, there is also ?! which is a "negative look ahead". Although clearly the following would not be the best way to achieve the result, if we wanted to only put a dot after the last character in the string, we could use the same UDF with the pattern "(.)(?!.)" meaning "(any character)(not followed by any any character)". This would just match the final character in the string.

 

[Scott Huish]

Cool. Thanks.

 

[Peter_SSs]

No problem. I just edited to add a further bit. Not sure if you saw that.

 

[lsteffen]

I have a question about this topic.

I need to have my date field surrounded by spaces.

End result would be:

[space]111018[space]

Thank you.
Liz