Space after every 3 characters in excel

[excelinexcel7]

Hi Everyone,

I have a list of 1000 columns where the data is like this:

ABCDEFGHIJKL
DCABHG
ABTGYU

I want to add a space after every 3 characters. Please advise, how can I do this in excel.

So the output should be like this.

ABC DEF GHI JKL
DCA BHG
ABT GYU



Thanks
Jay

 

[kevatarvind]

one more this also will work and easy to use

=MID(B9,1,3)&" "&MID(B9,4,3)&" "&MID(B9,7,3)&" "&MID(B9,10,3)&" "&MID(B9,13,3)

 

[Rick Rothstein]

one more this also will work and easy to use

=MID(B9,1,3)&" "&MID(B9,4,3)&" "&MID(B9,7,3)&" "&MID(B9,10,3)&" "&MID(B9,13,3)

You might want to put a TRIM function call around that formula in order to get rid of any trailing spaces resulting from short text strings in the cell.

=TRIM(MID(B9,1,3)&" "&MID(B9,4,3)&" "&MID(B9,7,3)&" "&MID(B9,10,3)&" "&MID(B9,13,3))

Of course, your formula will not work correctly for text strings longer than 15 characters.

 

[kevatarvind]

You might want to put a TRIM function call around that formula in order to get rid of any trailing spaces resulting from short text strings in the cell.

=TRIM(MID(B9,1,3)&" "&MID(B9,4,3)&" "&MID(B9,7,3)&" "&MID(B9,10,3)&" "&MID(B9,13,3))

Of course, your formula will not work correctly for text strings longer than 15 characters.

this is the sample of Formula user has to change this formula as per his requirements if the text strings is longer then 15 he has to add

 

[Joe4]

Another option:

If you aren't adverse to using VBA, you could also create a User Defined Function to do this. The advantage to this method is that you could use loops, so it doesn't matter how long or short the entry is. You wouldn't have to create an insanely long formula to handle the longest entries. Just one short "catch-all" function call.

I can whip something up, if you are interested. But I won't bother if you cannot or do not want to use VBA.

 

[excelinexcel7]

Thanks Joe ! VBA could be very helpful. That way I don't have to copy this formula everytime.

 

[Rick Rothstein]

Thanks Joe ! VBA could be very helpful. That way I don't have to copy this formula everytime.

I see Joe is offline right now, so let me jump in and give you a UDF (user defined function) that you can use...

Function ThreeChars(ByVal S As String) As String
  Dim X As Long
  For X = 3 * Int(Len(S) / 3) + 1 To 3 Step -3
    S = WorksheetFunction.Replace(S, X, 0, " ")
  Next
  ThreeChars = S
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ThreeChars just like it was a built-in Excel function. For example,

=ThreeChars(A1)

 

[Joe4]

Thanks for the assist Rick. I have been offline for a while.

 

[Peter_SSs]

I was looking at this yesterday and obviously Rick's code is compact and does what the OP asked. However, more for interest than anything else, I was wondering if possibly there was another way, avoiding the repeated call to a WorksheetFunction which tends to slow code. I didn't come up with anything but have had another look today. This option is marginally faster, about 10%, but unless there was a LOT of rows and/or a LOT of recalculations required there wouldn't be much point in changing.

Function ThreeChrs(ByVal s As String) As String
  Dim a
  Dim i As Long
  
  a = Split(Replace(StrConv(s, vbUnicode), Chr(0), Chr(0) & Chr(0)), Chr(0))
  For i = 6 To Len(s) * 2 Step 6
    a(i - 1) = " "
  Next i
  ThreeChrs = Join(a, "")
End Function

The function below has the added flexibility of being able to add any character(s) after any set amount of spacing, and I know Rick's could just as easily be modified for this if required, but since I've written it I thought I might as well post it too. This code is slightly slower than the above. For example, used to add a space after every 3 characters like the previous code, it takes almost identical time to Rick's code (tested over 10,000 rows).

So, for adding a space after every 3 characters it could be used like this:
=AddChars(A1,3)

But if you wanted to add, say, "#?" after every 4th character, then
=AddChars(A1,4,"#?")

And if you just wanted a space after every character:
=AddChars(A1)

Function AddChars(ByVal s As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
  Dim a
  Dim i As Long
  
  a = Split(Replace(StrConv(s, vbUnicode), Chr(0), Chr(0) & Chr(0)), Chr(0))
  For i = Spacing * 2 To Len(s) * 2 Step Spacing * 2
    a(i - 1) = InsertText
  Next i
  AddChars = Join(a, "")
End Function

 

[Rick Rothstein]

The function below has the added flexibility of being able to add any character(s) after any set amount of spacing, and I know Rick's could just as easily be modified for this if required, but since I've written it I thought I might as well post it too. This code is slightly slower than the above. For example, used to add a space after every 3 characters like the previous code, it takes almost identical time to Rick's code (tested over 10,000 rows).

Function AddChars(ByVal s As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
  Dim a
  Dim i As Long
  
  a = Split(Replace(StrConv(s, vbUnicode), Chr(0), Chr(0) & Chr(0)), Chr(0))
  For i = Spacing * 2 To Len(s) * 2 Step Spacing * 2
    a(i - 1) = InsertText
  Next i
 AddChars = Join(a, "")
End Function

You need to replace the line I highlighted in red with these lines of code...

  s = Join(a, "")
  If Right(s, Len(InsertText)) = InsertText Then s = Left(s, Len(s) - Len(InsertText))
  AddChars = s

The reason is that if the length of the text in the 's' argument is an exact multiple of what is in the 'Spacing' argument, you get an "extra" copy of what is in the 'InsertText' argument placed at the end of what is assigned to AddChars. For example, run this before making the change I suggested to see the problem...

MsgBox AddChars("abcdefghijklmnopqrst", 4, "@")

I took a quick look at seeing what I could come up with to do what your function above does and here is what I came up with (I have no idea how it will compare time-wise to your function)...

Function AddChars(ByVal s As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
  Dim X As Long, Position As Long
  AddChars = String(Len(s) * (1 + 1 / Spacing), Chr(1))
  For X = 1 To Len(s) Step Spacing
    Mid(AddChars, Position + 1, Spacing) = Mid(s, X, Spacing)
    Position = Position + Spacing + 1
  Next
  AddChars = Replace(AddChars, Chr(1), InsertText)
  If Right(AddChars, Len(InsertText)) = InsertText Then AddChars = Left(AddChars, Len(AddChars) - Len(InsertText))
End Function

 

[Peter_SSs]

You need to replace the line I highlighted in red ...

The reason is that if the length of the text in the 's' argument is an exact multiple of what is in the 'Spacing' argument, you get an "extra" copy of what is in the 'InsertText' argument placed at the end of what is assigned to AddChars.

(You are most likely right, but in hindsight I'm claiming ..) It depends how strictly you interpret the stated requirement:

I want to add a space after every 3 characters.

It doesn't say "except the last", and none of the expected result examples are definitive in that regard.

You need to replace the line I highlighted in red with these lines of code

  s = Join(a, "")
  If Right(s, Len(InsertText)) = InsertText Then s = Left(s, Len(s) - Len(InsertText))
  AddChars = s

Assuming your interpretation above is correct, then I do need to modify my code, but I wouldn't use those lines as they could also produce incorrect results, see below.

I would just make this change

For i = Spacing * 2 To Len(s) * 2 - 1 Step Spacing * 2

Example of incorrect result using your suggested change:
A1: "AAAAX"
Formula: =AddChars(A1,4,"X")
Expected result: "AAAAXX"
Result using your suggested change: "AAAAX"
(Similar issue with your new function)

Now, just to show I'm not "picking", your new code is ball-park twice as fast as mine.
May end up even faster after fixing the issue above as I think you should be able to use something more like I suggested for dealing with "exact multiple" strings and I suspect that will shave a little more time.