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

 

[Scott Huish]

What is the criteria for where the . should go?

 

[Rick Rothstein]

Same type of question, however, I need to add . back into a column of email address that were incorrectly changed to have the . removed

Give this a try...

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

 

[HarryS]

Try something along these lines

Function NumB(k$) As Boolean    ' is it a number character
    NumB = k >= "0" And k <= "9"
End Function
Private Sub CommandButton1_Click()
    'use like
    Range("d3") = DotIt(Range("d2"))
End Sub
Function DotIt$(w$) ' to dot on change of character type
    Dim WA$, Numeric As Boolean, CC%
    'set the first character
    WA = Left(w, 1)
    Numeric = NumB(WA)
    'work through the characters
    For CC = 2 To Len(w)
        If Numeric <> NumB(Mid(w, CC, 1)) Then WA = WA + "."    ' changed
        'deal with character
        Numeric = NumB(Mid(w, CC, 1))
        WA = WA + Mid(w, CC, 1)
    Next CC
    DotIt = WA
End Function

 

[HarryS]

I did not see Rick's excellent post when I first visited.. so it is a repeat solution ( again )
Good to see a 2004 thread still surviving and drawing interest
the extra function Numb used so that it can further define the conditions of the change
without too much changing .
That is what is wanted from as string like
vcxxdfvsd56221 23 dsfsdfd safddsfdasf 2354 fdfas as34324#$$%&)()_))(*(()_)<> fvsdf
where the characters about the space remain the same or change, what of multiple spaces
dsfd 3432423 43435gerdg 545565%^^&**(()
what about we do not complicate it too much.. time for breakfast in Aus.

 

[Mpitre]

Hi Guys, Thanks for the post. I'm also trying to add a space in a text string. In my case I'd like to add a space between the "y" and "2" in "Games Barney20171027 $1250". I tried modifying the reply from the this thread =LEFT(M5,13)&" "&RIGHT(M5,LEN(M5)-14) but my text displays as

Games Barney 0171027 $1250. Any suggestions on why my 2 is dropping out of my year 2017?

<tbody>
</tbody>

<tbody>
</tbody>

 

[mumps]

Try:
=LEFT(A1,12)&" "&RIGHT(A1,LEN(A1)-12)

 

[Mpitre]

Hi Mumps, I tweeked your post to fit my spreadsheet and produced Games Barney 20171027 $1250

by using =LEFT(M5,12)&" "&RIGHT(M5,LEN(M5)-12). But I think it's wrong, when I copy it down, my spaces are absolute and the next line looks like Music David2 0171020 $1054, putting a space in the wrong place, any suggestions?​

 

[steve the fish]

How about:

=TRIM(REPLACE(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),0," "))

 

[jtakw]

Hi,

This will also work if all your years start with 20xx:

Book1
	M	N
5	Games Barney20171027 $1250	Games Barney 20171027 $1250
6	Music David20171020 $1054	Music David 20171020 $1054
7	Something20171112 $9999	Something 20171112 $9999
8	Some Other Thing20170204 $1111	Some Other Thing 20170204 $1111
Sheet32

Cell Formulas
Range	Formula
N5	=REPLACE(M5,FIND("20",M5),0," ")

 

[Mpitre]

I bow down to you Excel master fish. That worked out great. I copied it down 500 rows putting a space exactly where I needed it.