#1
|
|||
|
|||
Changing Row of numbers into column or similiar
Hi,
I would like to explain my problem with example Suppose have large row of numbers such as 3533080824397 3533080824240 353308085776 3533080845110 3533080851279 3533080850682 3533080843470 35330808436 353308084366 and i want to transfer them into one per line 35330808297 3533080824240 353308085776 3533080845110 3533080851279 35330808582 35330808430 35330808436 353308084366 Currently I chang them to above form manually using enter buttom . Is there any quick method out there that can help me save time? many thanks |
#2
|
|||
|
|||
With your example, it's hard to tell a few things such as, are all the value in one cell of the row, are they all the same size in digits?
Can you past a sample workbook that would provide details with the above questions? |
#3
|
|||
|
|||
Thank you for your reply and sorry i didnt make it clear enough.
I sell unlocking codes and the numbers i am dealing with are 15 digits imeis. When quantity is sold i get about 5 imeis per row each one separated by space and depending on quantity there can be many rows. My system require me to convert the list to one imei per line . Thnaks |
#4
|
|||
|
|||
I do not know of a formula that will do this, but try this macro.
Code:
Sub test() Dim x As Variant Dim y As String Dim j As Long: j = 0 Dim LR As Long Dim i As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row With Cells(i, 1) x = Split(.Value, " ") LR = Range("A" & Rows.Count).End(xlUp).Row If Cells(5, 1) = "" Then Else j = j + 1 End If With .Offset(LR - j).Resize(UBound(x) + 1) .Value = Application.Transpose(x) End With End With Next i For i = 5 To Range("A" & Rows.Count).End(xlUp).Row Cells(i, 1).Value = "'" & Cells(i, 1).Value Next i End Sub
Since you did not post a sample other than in post #1, my thought is the sample covers the first four rows of column A |
#5
|
||||
|
||||
If the data are supplied as a text file, you really don't need Excel or a macro for this. All you need do is open the text file (even NotePad will do) and use Find/Replace to replace the spaces with line breaks.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Changing Column's Width in Tables | mohsen.amiri | Word Tables | 4 | 11-03-2014 01:04 PM |
How to change the formatting of numbers without changing the numbers? | sullengirl | Word | 2 | 01-27-2014 03:06 PM |
Column Width keeps changing | alsmith | Word Tables | 1 | 05-25-2013 02:09 AM |
Changing Caption Numbers | RunningPirate | Word | 2 | 09-03-2011 07:42 AM |
changing column formats | herbycanopy | Excel | 2 | 03-30-2010 09:11 PM |