Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-12-2018, 02:22 PM
foneunlocker foneunlocker is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2014
Posts: 7
foneunlocker is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-12-2018, 03:46 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 478
jeffreybrown will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 01-12-2018, 05:42 PM
foneunlocker foneunlocker is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2014
Posts: 7
foneunlocker is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 01-12-2018, 05:59 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 478
jeffreybrown will become famous soon enough
Default

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
Paste code in a Normal module
  • Where to paste code
  • Highlight macro to copy >> Ctrl + C >> Open your workbook
  • Alt + F11 >> opens the Visual Basic Editor (VBE)
  • Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
  • Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
  • Paste code >> Ctrl + V (right side of screen)
  • Alt + Q >> exits VBE and returns to Excel
  • Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

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
Reply With Quote
  #5  
Old 01-12-2018, 06:38 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,458
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 06:50 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft