arrange within a cell

[ajith]

Hello,
Is it possible to arrange the multi-line contents within a single cell into columns

eg. if within a cell if it is like this
123445 ford ecospot
346 fiat
23 Nissan

We have to make it into, all the numbers one under the other and names one under the other within the same cell

 

[ajith]

and each ie numbers and names aligned to left

 

[JoeMo]

Assuming the "lines" are separated by a carriage return (alt + enter), first select all the cells you want to apply this to. Then run the macro below:

Sub TxtToCols()
'select cells first then run this macro
Dim c As Range, Parts
Application.ScreenUpdating = False
For Each c In Selection
       If InStr(c.Value, Chr(10)) = 0 Then
              c.Offset(0, 1).Value = c.Value
              Exit Sub
       End If
       Parts = Split(c.Value, Chr(10))
       For i = LBound(Parts) To UBound(Parts)
              c.Offset(0, i + 1) = Parts(i)
       Next i
       With c.Offset(0, 1).Resize(1, UBound(Parts) + 1)
              .WrapText = False
              .EntireColumn.AutoFit
       End With
Next c
Application.ScreenUpdating = True
End Sub

 

[ajith]

Assuming the "lines" are separated by a carriage return (alt + enter), first select all the cells you want to apply this to. Then run the macro below:

Sub TxtToCols()
'select cells first then run this macro
Dim c As Range, Parts
Application.ScreenUpdating = False
For Each c In Selection
       If InStr(c.Value, Chr(10)) = 0 Then
              c.Offset(0, 1).Value = c.Value
              Exit Sub
       End If
       Parts = Split(c.Value, Chr(10))
       For i = LBound(Parts) To UBound(Parts)
              c.Offset(0, i + 1) = Parts(i)
       Next i
       With c.Offset(0, 1).Resize(1, UBound(Parts) + 1)
              .WrapText = False
              .EntireColumn.AutoFit
       End With
Next c
Application.ScreenUpdating = True
End Sub

Hello sir,
thanks for the effort. I have entered the entire data in B38 with CHAR(10) after each line, separating each data in each line using space. But when the macro after selecting the cell it is increasing the column width of K.

 

[Peter_SSs]

Hello sir,
thanks for the effort. I have entered the entire data in B38 with CHAR(10) after each line, separating each data in each line using space.

It isn't clear to me what result you are looking for. If all that is entered into B38, then after any adjustment has been made, will that data
a) still be all in cell B38, only aligned better, or
b) have all the numbers be in, say, C38, and all the descriptions in D38, or
c) have the numbers in, say, C38:C40 and the descriptions in D38:40, or
d) something else?

 

[ajith]

Sir,
The entire data is in B38. It has totally 35 lines one below the other separated by CHAR(10). The number of lines in the cell will vary depending on the users profile. Each line has four data, each separated by a space. The width of each data of each line varies in length. As I am not aligning them manually, the data in the cell will have a zigzag appearance (vertically). What I am trying to find is a macro or formula to align it so that the 1st data of all the lines are one below the other and 2nd data of all the lines one below the other. like that for all the four data of each line. So that it will be neat and will look like a table. I hope it is better now.

 

[ajith]

Sir,
The entire data is in B38. It appears in B38 based on the value given in another sheet. The formula given in B38 for a single line is shown below.

=INDIRECT("Sheet3!x"&(3+N$16))&" "&INDIRECT("Sheet3!y"&(3+N$16))&" "&INDIRECT("Sheet3!z"&(3+N$16))&" "&INDIRECT("Sheet3!aa"&(3+N$16))&IF(INDIRECT("Sheet3!ab"&(3+N$16))="","",CHAR(10))&......................

 

[Peter_SSs]

Why are pulling all these things into a single cell?
One of the whole points of Excel is that it has columns & rows so that you can put individual like things in them and they line up automatically.

 

[ajith]

Sir,
I do agree with that. But why I had put it in one cell was because many a times majority of these lines in B38 will not have data, ie. it will be having four or six lines commonly. But I have to provide the provision for any odd user who has more to input. Ok Rest will be empty. In B39 I have another paragraph with two or four sentences to appear for the final report. So if I put the above 35 lines in 35 rows which most of the time will be empty, then there will be a wide gap in between these rows (35 no.s) and the last paragraph of my report. So that is why I did it it like that.
Or else if I put these in 35 separate lines, I have to look for a macro which will adjust the cell height of all the empty rows to zero. So that the gap won't appear. As the number of empty rows can vary from 34 to 0 the macro should find it out automatically and the sheet should be reusable. The rows with data will come only in the beginning. So now I think this will be simpler and better. But how about a macro now.

 

[ajith]

Sir,
I do agree with that. But why I had put it in one cell was because many a times majority of these lines in B38 will not have data, ie. it will be having four or six lines commonly. But I have to provide the provision for any odd user who has more to input. Ok Rest will be empty. In B39 I have another paragraph with two or four sentences to appear for the final report. So if I put the above 35 lines in 35 rows which most of the time will be empty, then there will be a wide gap in between these rows (35 no.s) and the last paragraph of my report. So that is why I did it it like that.
Or else if I put these in 35 separate lines, I have to look for a macro which will adjust the cell height of all the empty rows to zero. So that the gap won't appear. As the number of empty rows can vary from 34 to 0 the macro should find it out automatically and the sheet should be reusable. The rows with data will come only in the beginning. So now I think this will be simpler and better. But how about a macro now.

Sir,
Sub ShrinkToFit()
Dim ws As Worksheet
Dim Rg As Range
Dim Ro As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set ws = Worksheets("Sheet2")
Set Rg = ws.Range(ws.Cells(38, 73), ws.Cells(ws.Rows.Count, 1).End(xlUp))
For Each Ro In Rg
If Len(Ro) <= 0 Then
Ro.EntireRow.RowHeight = 0
Else
Ro.EntireRow.AutoFit
End If
Next Ro
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

This macro is reducing the row height of even rows with data and also runtime error 13. Where it could be corrected