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

 

[Rick Rothstein]

(You are most likely right, but in hindsight I'm claiming ..) It depends how strictly you interpret the stated requirement:It doesn't say "except the last", and none of the expected result examples are definitive in that regard.

True, but I am betting that I am right ... I guess we will have to wait for the OP to come back to the thread to explain exactly what was wanted.

Assuming your interpretation above is correct, then I do need to modify my code,
(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.

I would never consider comments, suggestions and/or corrections to anything I post to be "picking"... I think it is our collective responsibility to do such things when we see messages warranting them so that the originator of the thread gets his/her best possible answer or, at minimum, gets a credible set of possible solutions for him/her to choose from. With that said, your comments sent me back to the "drawing board" and I came up with what I think is an improved code procedure for my last posted function (it might even be a tad faster)...

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) + Int((Len(S) - 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)
End Function

 

[Peter_SSs]

(it might even be a tad faster)...

Well, I think we are just about down to the "Death by Tweakage" stage now but yes, that is about 1.5% faster than your previous code. However,

a) this one doesn't like blank cells so it would still need a further tweak as the previous codes all returned blanks for blanks.

b) your methods being faster got me to looking more at string manipulations and I came up with this one that is pretty straight-forward and about 3% faster again (each timed 6 times over 10,000 rows)

Function AddCharsNew(ByVal s As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
  Dim i As Long, startpos As Long
  
  startpos = Len(s) - IIf(Len(s) Mod Spacing = 0, Spacing, Len(s) Mod Spacing)
  For i = startpos To Spacing Step -Spacing
    s = Left(s, i) & InsertText & Mid(s, i + 1)
  Next i
  AddCharsNew = s
End Function

 

[Rick Rothstein]

a) this one doesn't like blank cells so it would still need a further tweak as the previous codes all returned blanks for blanks.

I must have read your comment over about a dozen times and each time I read "blank cells" as "blank spaces" (maybe because I did all my testing in VB's Immediate Window and not on a worksheet) and could not figure out why you said my code did not work with them. Finally, it dawned on me that you had written "cells", not "spaces". The fix for my code so that it does not "choke" when an empty string is passed into the first argument was simple and should not affect its slight speed advantage over my older code...

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) + Int((Len(S) + (Len(S) > 0)) / 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)
End Function

I am wondering, though, whether the following modification to this code might not be faster (it removes one of the repeated +1 from inside the loop)?

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) + Int((Len(S) + (Len(S) > 0)) / Spacing), Chr(1))
  Position = 1
  For X = 1 To Len(S) Step Spacing
    Mid(AddChars, Position, Spacing) = Mid(S, X, Spacing)
    Position = Position + Spacing + 1
  Next
  AddChars = Replace(AddChars, Chr(1), InsertText)
End Function

Just out of curiosity, how are you performing your timing tests for your "each timed 6 times over 10,000 rows" test... how do you measure the speed of a function in a cell?

b) your methods being faster got me to looking more at string manipulations and I came up with this one that is pretty straight-forward and about 3% faster again (each timed 6 times over 10,000 rows)

Function AddCharsNew(ByVal s As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
  Dim i As Long, startpos As Long
  
  startpos = Len(s) - IIf(Len(s) Mod Spacing = 0, Spacing, Len(s) Mod Spacing)
  For i = startpos To Spacing Step -Spacing
    s = Left(s, i) & InsertText & Mid(s, i + 1)
  Next i
  AddCharsNew = s
End Function

While I do not doubt your results, I am a little surprised by them. I deliberately avoided doing the repeated concatenations you used because concatenations are a slower process than performing the, what I call, string-stuffing method that I used (where Mid is used on the left side of the assignment operation). True, I add an additional line of code to the loop, but it is a simple addition statement which is a fairly fast operation in VB, so I was not expecting my code to be slower than your latest code. Perhaps it hinges on my use of the single Replace function call at the end of the procedure (Replace is not one of VB's fastest functions, but it is not repeated inside the loop, so I didn't think it would produce that much of a drag on the execution speed). Anyway, I note you used the IIf function in your code... as it turns out, that is one of VB's slowest functions. So, at the risk of extending the "Death by Tweakage" stage a little more, see if this modification to your code (in which I replaced the IIf function call with a logical expression) speeds things up of not...

Function AddCharsNew(ByVal S As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
  Dim i As Long, startpos As Long
  startpos = Len(S) - (Len(S) Mod Spacing) + Spacing * (Len(S) Mod Spacing = 0)
  For i = startpos To Spacing Step -Spacing
    S = Left(S, i) & InsertText & Mid(S, i + 1)
  Next i
  AddCharsNew = S
End Function

 

[Peter_SSs]

I am wondering, though, whether the following modification to this code might not be faster (it removes one of the repeated +1 from inside the loop)?

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) + Int((Len(S) + (Len(S) > 0)) / Spacing), Chr(1))
  Position = 1
  For X = 1 To Len(S) Step Spacing
    Mid(AddChars, Position, Spacing) = Mid(S, X, Spacing)
    Position = Position + Spacing + 1
  Next
  AddChars = Replace(AddChars, Chr(1), InsertText)
End Function

By my testing, see below, this is about 7% slower than the code in post #22.

Just out of curiosity, how are you performing your timing tests for your "each timed 6 times over 10,000 rows" test... how do you measure the speed of a function in a cell?

I hope that I am applying it correctly. I'm using the codes provided just below where this link lands you.

For this thread I've generated 10,000 rows (started much smaller of course) of random data strings of 50 characters or less with characters made up roughly two thirds [a-zA-Z], one sixth [0-9] and one sixth at random from a few special characters " !#$%&()*+". I can provide the code producing the data if you want. I manually made sure I had a blank and an exact multiple of 'Spacing' near the top of my data as they were likely candidates to fail.

I then populated adjacent columns with functions I was comparing, selected either B1:B10000 or C1:C10000 and ran the 'RangeTimer' macro from the above link. I usually ran the timer a couple of times then Saved the file before running the timer again. Sometimes I completely removed the function I wasn't timing to ensure it wasn't actually having an impact, but it seemed to make no difference - as it shouldn't given the timing code.

While I do not doubt your results, I am a little surprised by them. I deliberately avoided doing the repeated concatenations you used because concatenations are a slower process than performing the, what I call, string-stuffing method that I used (where Mid is used on the left side of the assignment operation). True, I add an additional line of code to the loop, but it is a simple addition statement which is a fairly fast operation in VB, so I was not expecting my code to be slower than your latest code. Perhaps it hinges on my use of the single Replace function call at the end of the procedure (Replace is not one of VB's fastest functions, but it is not repeated inside the loop, so I didn't think it would produce that much of a drag on the execution speed).

I am not able to add anything as to the reason.

Anyway, I note you used the IIf function in your code... as it turns out, that is one of VB's slowest functions. So, at the risk of extending the "Death by Tweakage" stage a little more, see if this modification to your code (in which I replaced the IIf function call with a logical expression) speeds things up of not...

Function AddCharsNew(ByVal S As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
  Dim i As Long, startpos As Long
  startpos = Len(S) - (Len(S) Mod Spacing) + Spacing * (Len(S) Mod Spacing = 0)
  For i = startpos To Spacing Step -Spacing
    S = Left(S, i) & InsertText & Mid(S, i + 1)
  Next i
  AddCharsNew = S
End Function

This and the post #22 code are so close (within 0.05%) that I wouldn't say there was any difference at all.

However, I'm glad you suggested that new startpos line. I was originall trying write something like that but couldn't get my head around the True condition for (Len(S) Mod Spacing = 0) returning -1 in vba as opposed to +1 in a worksheet formula - so I resorted to the IIf.


Edit: An afterthought is that I guess it is possible that using different machines could make a difference to relative speeds. This post has links to a couple of other threads where another poster and I have compared speeds (macro rather than calculation but it's possible the same thing happens with calculations, particularly ones that use vba), with completely opposite results.