Solved: Separating between each two digits

[YasserKhalil]

Hi guys
I have a range of numbers which differs in the number of digits
I want to separate between each two digits with a space..
Note that the number of digits is unknown and changeable
for example the number 15426854645 should be like that
15 42 68 56 65 5

[p45cal]

[VBA]=TRIM(TEXT(A2,CHOOSE(MOD(LEN(A2),2)+1,"## ## ## ## ## ## ## ## ## ## ##","## ## ## ## ## ## ## ## ## ## ## #")))[/VBA]
It's a formula, not vba, you may need to increase the number of # characters if your numbers are longer.

[YasserKhalil]

Thank you Mr. p45cal
Your great formula works just with numbers..
What if the number was a mixture of text and numbers?

[p45cal]

Thank you Mr. p45cal
Your great formula works just with numbers..
What if the number was a mixture of text and numbers?

Ah, the old moving-the-goalposts gambit..

some examples of before and after perhaps?

[GTO]

Ah, the old moving-the-goalposts gambit..

some examples of before and after perhaps?

A quiet rainy day off started with laughter; nice!

Well, here was my shot, modified as guessing...

As a UDF, array UDF or called in vba...
[vba]
Option Explicit

Sub exa()
Dim rng As Range

Set rng = Range("A2:A20")
rng.Value = SplitNumString(rng)
End Sub

Function SplitNumString(CellRange As Range) As Variant
Static REX As Object
Dim vntCellVal As Variant, _
aryVals As Variant, _
x As Long, _
y As Long

If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
REX.Global = True
REX.Pattern = "([0-9]{2}|[a-zA-Z]{2}|[0-9][a-zA-Z]|[a-zA-Z][0-9])(?=.)"
End If

aryVals = CellRange.Value

If IsArray(aryVals) Then
For x = 1 To UBound(aryVals)
For y = 1 To UBound(aryVals, 2)
aryVals(x, y) = REX.Replace(aryVals(x, y), "$1 ")
Next
Next
SplitNumString = aryVals
Else
SplitNumString = REX.Replace(aryVals, "$1 ")
End If
End Function[/vba]

Hope that helps,

Mark

[Tinbendr]

[vba]Sub AddASpace()

Dim aCell As Range

For Each aCell In Range("A1:A19")
aCell = WorkHorse(aCell.Text)
Next

End Sub
Function WorkHorse(aCell As String) As String

Dim TempCell As String
Dim A As Long

For A = 1 To Len(aCell) Step 2
TempCell = TempCell & Mid(aCell, A, 2) & " "
Next

WorkHorse = Trim(TempCell)

End Function[/vba]

[YasserKhalil]

Mr. Mark
Mr. Tinbendr
Your solutions are perfect ... They are both wonderful
Thank you very much for your help