convert multiline cells to columns in csv?

muhmath2002

New Member
Joined
Dec 16, 2019
Messages
26
Office Version
  1. 2007
Platform
  1. Windows
hi all
i have csv file all columns contains multiline cells ( two values )
i need to convert every column to 2 column with first line value and second line value
is there fast method or tool or software
regards
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
as in the cells contain 2 values separated by a space ?

eg: 1234 987 and you want it in 2 cells as 1234 and another 987


Book1
ABC
11234 9871234987
1
Cell Formulas
RangeFormula
B1B1=LEFT(A1,FIND(" ",A1))
C1C1=RIGHT(A1,LEN(A1)-FIND(" ",A1))


Then simply copy and paste back values
 
Last edited:
Upvote 0

cooper645

thanks for formulas
my file ,first line and second line contains strings with spaces
we need vba code to split all columns to two columns with first line and second line (new sheet)
first line and second line like wrap text in excel
 
Upvote 0
VBA Code:
Sub LineBreak()

Application.ScreenUpdating = False


Dim Str1 As String, Str2 As String, Orig As String
Dim Lcol As Long, Lrow As Long, i As Long, j As Long

Lcol = Cells(1, Columns.Count).End(xlToLeft).Column

'Insert Blank Columns
Cells(1, 1).Select
 For i = 1 To Lcol
    ActiveCell.Offset(0, 1).EntireColumn.Insert 'Shift:=xlToRight
    ActiveCell.Offset(0, 2).Select
 Next i
    
Lcol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To Lcol Step 2
Lrow = Cells(Rows.Count, i).End(xlUp).Row
    For j = 1 To Lrow
        Orig = Cells(j, i)
 Str1 = Left(Orig, InStr(Orig, vbLf))
        Str2 = Right(Orig, Len(Orig) - InStr(Orig, vbLf))
        Cells(j, i) = Str1
        Cells(j, i).Offset(0, 1) = Str2
    Next j
Next

Application.ScreenUpdating = True
End Sub
 
Upvote 0
A couple of comments
  • Not sure if you are aware that the above suggested code leaves that line break character on the end of all the left-hand column values for each pair of columns. It is easily fixed of course.
  • You also asked for a fast method. I don't know how big your data is but for my sample data with 500 rows and 15 columns the code below took 0.06 seconds compared to 1.3 seconds for the code above. That is, this was 20+ times faster.
VBA Code:
Sub SplitEm()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, uba2 As Long, pos As Long

  a = ActiveSheet.UsedRange.Value
  uba2 = UBound(a, 2)
  ReDim b(1 To UBound(a), 1 To 2 * uba2)
  For i = 1 To UBound(a)
    For j = 1 To uba2
      pos = InStr(a(i, j) & vbLf, vbLf)
      b(i, j * 2 - 1) = Left(a(i, j), pos - 1)
      b(i, j * 2) = Mid(a(i, j), pos + 1)
    Next j
  Next i
  Range("A1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top