Formatting string in groups of letters...

[HBj]

Hi,

How can a string without spaces be formatted to display the string in groups
of five characters.
Example:

246 81357 99876 54321

Only the left-most group can contain less than 5 characters

 

[Stefi]

=LEFT(A2,LEN(A2)-15)&" "&MID(A2,LEN(A2)-14,5)&" "&MID(A2,LEN(A2)-9,5)&"
"&RIGHT(A2,5)

Regards,
Stefi

„HBj†ezt írta:

 

[HBj]

Sorry, you misunderstood my problem. I mean only the DISPLAY FORMAT (in the
same manner as thousand separator). The string is physically untouched, only
the way it is displayed is the problem.

Also, the string length may vary from 3 characters to about 40.

Håkan

 

[David Biddulph]

A string is not affected by display formats.

 

[Stefi]

I understood your request, but I forgot to mention - like David didi it -
that strings cannot be formatted. I suggested a workaround, and here is a
modified solution to handle strings up to either 40 characater length:

=RIGHT(LEFT($A$2,LEN($A$2)-15),5)&" "&RIGHT(LEFT($A$2,LEN($A$2)-10),5)&"
"&RIGHT(LEFT($A$2,LEN($A$2)-5),5)&" "&RIGHT(A2,5)

This is good up to 20 character length, if you want to expand length to 25,
insert
RIGHT(LEFT($A$2,LEN($A$2)-20),5)&" "
at the beginning of the formula, and so on.

You can achieve an effect as if the string would be formatted, if you hide
the column containing the source strings and use the above formula in the
visible adjacent column.

Regards,
Stefi

„HBj†ezt írta:

 

[HBj]

I have tried with and expected "Format->Cells->Custom" etc. with some kind
of pattern. It's no problem to _modify_ the string to show what you want,
but it requires one more column and confusion when you need to change it
back to its original format.
So, it seems to me that I do better make a short VBA snip to add the spaces
for displaying it and a second to compress.

Rgds
Håkan

 

[Ron Rosenfeld]

Hi,

How can a string without spaces be formatted to display the string in groups
of five characters.
Example:

246 81357 99876 54321

Only the left-most group can contain less than 5 characters

You cannot accomplish this with formatting. The best you can do, while leaving
the original string unchanged, is to display it in the desired fashion in
another cell.

Here is a UDF (user-defined-function) that will take a string as input, remove
any existing spaces, and then output a string spaced according to your rules.

The default spacing is five characters, but optional arguments allow you to
specify any desired number of characters, and also to specify whether the
formatted string fills from the right (the default) or from the left (so that
the right-most group could contain less than L characters).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=InsertSpaces(A1)

in some cell.


=================================================
Option Explicit
Function InsertSpaces(sStr, Optional Length As Long = 5, _
Optional RightToLeft As Boolean = True)
Dim sFormat As String
Dim sTemp As String
sTemp = Replace(sStr, " ", "")

With Application.WorksheetFunction
sFormat = " " & .Rept("@", Length)
sFormat = .Rept(sFormat, .Ceiling(Len(sTemp) / Length, 1))
End With

If RightToLeft = False Then
sFormat = "!" & sFormat
End If

InsertSpaces = Trim(Format(sTemp, sFormat))

End Function
========================================



If you want to use the optional arguments, you could do that by entering a
formula of the type:

=InsertSpaces(A1,[num_spaces],[fill_direction])

for [fill_direction] -- FALSE will fill left-to-right.

--ron

 

[HBj]

Thank you Ron for your complete answer. Looks like you wrote this code for
this very case.

Håkan

Hi,

How can a string without spaces be formatted to display the string in
groups
of five characters.
Example:

246 81357 99876 54321

Only the left-most group can contain less than 5 characters

You cannot accomplish this with formatting. The best you can do, while
leaving
the original string unchanged, is to display it in the desired fashion in
another cell.

Here is a UDF (user-defined-function) that will take a string as input,
remove
any existing spaces, and then output a string spaced according to your
rules.

The default spacing is five characters, but optional arguments allow you
to
specify any desired number of characters, and also to specify whether the
formatted string fills from the right (the default) or from the left (so
that
the right-most group could contain less than L characters).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual
Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=InsertSpaces(A1)

in some cell.


=================================================
Option Explicit
Function InsertSpaces(sStr, Optional Length As Long = 5, _
Optional RightToLeft As Boolean = True)
Dim sFormat As String
Dim sTemp As String
sTemp = Replace(sStr, " ", "")

With Application.WorksheetFunction
sFormat = " " & .Rept("@", Length)
sFormat = .Rept(sFormat, .Ceiling(Len(sTemp) / Length, 1))
End With

If RightToLeft = False Then
sFormat = "!" & sFormat
End If

InsertSpaces = Trim(Format(sTemp, sFormat))

End Function
========================================



If you want to use the optional arguments, you could do that by entering a
formula of the type:

=InsertSpaces(A1,[num_spaces],[fill_direction])

for [fill_direction] -- FALSE will fill left-to-right.

--ron

 

[Ron Rosenfeld]

Thank you Ron for your complete answer.

You're welcome. Glad to help.

Looks like you wrote this code for
this very case.

I did!
--ron