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

--
Håkan

=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:

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

--
HÃ¥kan

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
"Stefi" wrote in message
...
=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:

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

--
Håkan

A string is not affected by display formats.
--
David Biddulph

"HBj" wrote in message
...
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
"Stefi" wrote in message
...
=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:

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

--
Håkan

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:

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
"Stefi" wrote in message
...
=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:

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

--
HÃ¥kan

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

"Stefi" wrote in message
...
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:

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
"Stefi" wrote in message
...
=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:

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

--
Håkan

On Thu, 5 Mar 2009 13:49:43 +0200, "HBj" wrote:

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

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

Håkan

"Ron Rosenfeld" wrote in message
...
On Thu, 5 Mar 2009 13:49:43 +0200, "HBj"
wrote:

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

On Fri, 6 Mar 2009 15:55:24 +0200, "HBj" wrote:

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