ADD SPACES

[Guest]

I need to add spaces in a tempory name. What I have been trying without any
luck is;

tmpName2 = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 11)
tmpName2 = Left(tmpName2, Len(tmpName2) - 13 & " " & -11 & " " - 8 & " " &
-2 & " ").

What I need for it to do is to subtract the last 11 characters in the active
workbook name and then add spaces in the 13th,11th, 8th and 2nd spaces so
that;

12345678901213141516171819292122;

would become
123456789012131415161;

then
12345678 90 121 314151 61;

It does not work. Any help is truly apprectiate.

 

[Dave Unger]

Ronbo,

This might give you an idea of accomplishing what you want.

Dave

Sub Parse()

Dim tmpName2 As String
tmpName2 = "12345678901213141516171819292122"
tmpName2 = Left(tmpName2, Len(tmpName2) - 11)
tmpName2 = Left(tmpName2, 8) & " " & Mid(tmpName2, 9, 2) & " " &
Mid(tmpName2, 11, 3) & " " & Mid(tmpName2, 14, 6) & " " &
Right(tmpName2, 2)
MsgBox (tmpName2)

End Sub

 

[Guest]

Thanks for the help, but I am not getting anywhere. Using your code I get
12345sss45 or characters 1 to 5 and then three blanks spaces then repeat
characters 45. I have tried revising but anm not getting anywhere.

Any other ideas?

 

[Dave Unger]

Ronbo,

Maybe I'm not understanding the question correctly. Using your
example, I started with a workbook name of 32 characters - your 1st
statement cuts off the right 11 characters, leaving a string of 21
characters to process. Is this right, or am I missing something?

Dave

 

[Guest]

There are actually 27 characters and it would go as follows;

111223333334445566666666666

Changed to;
111 22 333333 444 55

The first statements removes the last 11 characters and leaves
1112233333344455

What I need is the syntax to insert the spaces in the name.

 

[Myrna Larson]

The following works for me:

X = Left$(X, 16)
X = Format$(X,"000 00 000000 000 00")

and (surprisingly, given the 15-digit precision), it comes out correctly when
X = 9,999,999,999,999,999

 

[Myrna Larson]

Of course, if your data isn't digits, as you show, my previous suggeston is
useless. In that case, some code like this will help:


Dim i As Long
Dim j As Long
Dim X As String
Dim Y As String

Const Template As String = "___ __ ______ ___ __"
Y = Template

X = "ABCDEFGHIJKLMNOPQRSTUVWXYZa"
X = Left$(X, 16)
j = 0
For i = 1 To Len(X)
j = j + 1
If Mid$(Y, j, 1) = " " Then j = j + 1
Mid$(Y, j, 1) = Mid$(X, i, 1)
Next i

 

[Dave Unger]

Ronbo,

Myrna's method is very interesting, an excellent approach. Here's
another way, take your pick.

Dave

Sub AddSpace()

Dim tmpName2 As String
tmpName2 = "111223333334445566666666666"
tmpName2 = Left(tmpName2, Len(tmpName2) - 11)
tmpName2 = Left(tmpName2, 3) & " " & Mid(tmpName2, 4, 2) & " " _
& Mid(tmpName2, 6, 6) & " " & Mid(tmpName2, 12, 3) & " " _
& Right(tmpName2, 2)

MsgBox (tmpName2)

End Sub

 

[Dave Unger]

Hi Myrna,

I found your approach to this very interesting. I haven't been doing
this very long (as you've maybe discerned), hope you don't mind if I
ask you a couple of (maybe stupid) questions.

1 - why Format$ instead of just Format.

2 - I can't get it to quite work for me - the spacing is correct, but
the last 2 digits are always rounded, eg, I always get 111 22 333333
444 60 instead of 111 22 333333 444 55.
I must be doing something wrong, but can't seem to figure out what.

Thanks,

Dave

 

[Myrna Larson]

AS you find in Help, Format returns a variant; Format$ returns a string. Since
a string is what I want, there's no point in having VBA convert a string to a
variant, under the hood, then me convert it from a variant to a string. IOW,
it saves time. When working with strings, I always use the string version when
VBA offers a choice, i.e. LEFT$, RIGHT$, MID$, LCASE$, UCASE$, STRING$, etc.

Most people suggest you avoid using variants unless absolutely necessary. i.e.
if the data is text, define the variable AS STRING, and use the string
functions with it.

As I think I mentioned (in surprise), Excel has only 15 digits of precision. A
16 digit number will be rounded to 15, with a zero added as the 16th digit.

What puzzled me was that this didn't happen with the example I gave in my
other post.

 

[Dave Unger]

Thanks for the info Myrna,

Dave

 

[Dave Peterson]

I lost the other thread, but if you use Decimals in VBA, you can get more than
15 significant digits (I think 28).

Option Explicit
Sub testme()
Dim myDecimal As Variant
myDecimal = CDec(1) / 3
MsgBox myDecimal
End Sub

 

[Myrna Larson]

Yes, you can, but the worksheet doesn't support it, so if/when you transfer
the data back to the worksheet, digits past 15 are lost.

 

[Dave Peterson]

Oops. Didn't know you were returning the value to the worksheet.

(but you could return it as a string--yeah, I know you knew that <bg>.)