3 numbers in each cell aligned verticaly one number in each cell

  • Thread starter Thread starter Mikhail Bogorad
  • Start date Start date
M

Mikhail Bogorad

i have a bunch of records that have 3 numbers in each cell and are
presented like this one

cell B2= $0.00 $0.00 $0.00; cell C2 =$5,705,412.00 $2,400,000.00
$2,500,000.00 cell D2 = $5,909,484.00 $2,322,398.00 4,122,300.00

So what i want to do is to alighn all numbers vertically with one
number in each cell, so they'll look like the following:

B2=$0.00 C2=$5,705,412.00 D2 =$5,909,484.00
B3=$0.00 C3=$2,400,000.00 D3 =$2,322,398.00
B4=$0.00 C4=$2,500,000.00 D4=$4,122,300.00

i have like a hundred records like this
Any ideas?

Cheers
 
i have a bunch of records that have 3 numbers in each cell and are
presented like this one

 cell B2= $0.00 $0.00 $0.00; cell C2 =$5,705,412.00 $2,400,000.00
$2,500,000.00 cell D2 = $5,909,484.00 $2,322,398.00 4,122,300.00

So what i want to do is to alighn all numbers vertically with one
number in each cell, so they'll look like the following:

B2=$0.00    C2=$5,705,412.00  D2 =$5,909,484.00
B3=$0.00    C3=$2,400,000.00  D3 =$2,322,398.00
B4=$0.00    C4=$2,500,000.00  D4=$4,122,300.00

i have like a hundred records like this
Any ideas?

Cheers
Click to expand...
Try this which assumes text of $ and 3 numbers per cell.

Sub SASparsecells()
Dim i As Long
Dim x As Double
Dim j As Integer
Range("c2:c4").Copy Range("b2")
For i = 2 To Cells(2, Columns.Count).End(xlToLeft).Column
x = InStrRev(Cells(2, i), "$")
On Error Resume Next
Do Until x < 3
For j = 4 To 2 Step -1
'MsgBox Mid(Cells(2, i), x, 256)
Cells(j, i).Value = Mid(Cells(2, i), x, 256)
Cells(2, i) = Left(Cells(2, i), x - 1)
x = InStrRev(Cells(2, i), "$", x - 1)
Next j
Loop
Next i
End Sub
 
Back
Top