Insert blank space

[Ed Peters]

Hi,

I have a variable length data in a cell.

I need to be able to insert a blank space after the 3rd character from
the right if one does not already exist.

How would I go about doing this?

Thanks,

Ed

 

[Helmut Weber]

Hi Ed,

as this group is about programming,
a basic example about string handling.

You may to convert it into a function, if you like.

Sub Test5bb()
Dim sTmp As String
Dim sLft As String ' left part
Dim sRgt As String ' right part
sTmp = "abcdefghijklmno"
If Mid(sTmp, Len(sTmp) - 2, 1) <> " " Then
sLft = Left(sTmp, Len(sTmp) - 2)
sRgt = Right(sTmp, 2)
sTmp = sLft & " " & sRgt
End If
MsgBox sTmp
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Chip Pearson]

Try something like the following:

Sub AAA()
Dim Rng As Range
Dim S As String
Set Rng = Range("A3")
S = Rng.Text
If Len(S) >= 4 Then
If Mid(S, 3, 1) <> " " Then
S = Left(S, 2) & " " & Mid(S, 3)
End If
Rng.Value = S
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Ed Peters]

Try something like the following:

Sub AAA()
Dim Rng As Range
Dim S As String
Set Rng = Range("A3")
S = Rng.Text
If Len(S) >= 4 Then
If Mid(S, 3, 1) <> " " Then
S = Left(S, 2) & " " & Mid(S, 3)
End If
Rng.Value = S
End If
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consultingwww.cpearson.com
(email on the web site)









- Show quoted text -

Thanks guys!

Ed

 

[L. Howard Kittle]

Hi Ed,

You posted in the programming group so you must want a VBA solution and you
got two. Here is a formula solution you may find helpful... or not.

=IF(MID(A1,3,1)<>" ",LEFT(A1,3)&" "&MID(A1,4,LEN(A1)),A1)

Copy > Paste Special > Values to get rid of the formulas.

Regards,
Howard