PDA

View Full Version : Solved: Separating between each two digits



YasserKhalil
07-31-2010, 04:42 AM
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
07-31-2010, 06:01 AM
=TRIM(TEXT(A2,CHOOSE(MOD(LEN(A2),2)+1,"## ## ## ## ## ## ## ## ## ## ##","## ## ## ## ## ## ## ## ## ## ## #")))
It's a formula, not vba, you may need to increase the number of # characters if your numbers are longer.

YasserKhalil
07-31-2010, 06:19 AM
Thank you Mr. p45cal
Your great formula works just with numbers..
What if the number was a mixture of text and numbers?

p45cal
07-31-2010, 06:24 AM
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
07-31-2010, 10:58 AM
Ah, the old moving-the-goalposts gambit..

some examples of before and after perhaps?

:rofl: :clap: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...

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

Hope that helps,

Mark

Tinbendr
08-01-2010, 05:16 AM
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

YasserKhalil
08-01-2010, 02:14 PM
Mr. Mark
Mr. Tinbendr
Your solutions are perfect ... They are both wonderful
Thank you very much for your help