Hi,
My excel file Cell A1 has the following format (Multiple numbers in diff rows)
1234 23244
4434 121 1442
534 121223 12
How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434
(Cell C1) etc.. ?
Thanks for your help.
You can use a UDF to do this.
To enter this <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then insert/module and paste the
code below into the window that opens.
Select your data range. Then <alt-F8> and RUN the Rearrange macro.
As written, the numbers are returned as numeric values, so leading zeros will
not be present.
If you require preservation of leading zero's, then note the comment to change
the format of the destination cells.
Also note that if any of your Selected data range is in any of the destination
cells, they will be overwritten and not recoverable. So backup your data.
=================================================
Option Explicit
Sub Rearrange()
Dim c As Range
Dim sTemp
Dim i As Long
For Each c In Selection
sTemp = sTemp & c.Text & " "
Next c
sTemp = Application.WorksheetFunction.Trim(sTemp)
sTemp = Split(sTemp, " ")
Set c = Range("A1")
For i = 0 To UBound(sTemp)
With c(1, i + 1)
.NumberFormat = "General"
'use numberformat @ for returning values as text
'.NumberFormat = "@"
.Value = sTemp(i)
End With
Next i
End Sub
============================================
--ron