PDA

View Full Version : Macro to Separate Text in a Cell



fj1991
07-05-2016, 11:54 AM
Greetings,

I have an excel sheet which contains cells of information where each cell contains two lines of text separated by a blank line. I need to create a macro that will take the information from that cell, and print it to another sheet with the top line in one cell, and the bottom line in another. Any idea how I can program a macro to detect the whitespace between them?

Thanks!

mancubus
07-05-2016, 12:09 PM
welcome to the forum.
you can post your workbook as described in my signature. replace any sensitive data.

mdmackillop
07-05-2016, 12:52 PM
Sub Test()
Dim i As Long
For i = 1 To Selection.Cells.Count
Sheets(2).Cells(i, 1).Resize(, 3) = Split(Selection(i), Chr(10))
Next i
End Sub

jolivanes
07-05-2016, 01:46 PM
mdMackillop's looks a heck of a lot more professional but I have this ready so here you go.

The so called white space need to be a minimum of 5 spaces (spacer bar = Character 32) long.
If you're willing to take the risk, try it on a copy of your workbook.
Change references (Sheets, ranges) as required.

Sub Split_At_Multiple_Spaces()
Dim i As Long, sh3 As Worksheet
Dim a, b
Set sh3 = Sheets("Sheet3")
a = WorksheetFunction.Rept(Chr(32), 5)
With Worksheets("Sheet2")
For i = 2 To Cells(.Rows.Count, 1).End(xlUp).Row
b = Split(Cells(i, 1).Value, a)
sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1) = Trim(b(LBound(b)))
sh3.Cells(Rows.Count, 2).End(xlUp).Offset(1) = Trim(b(UBound(b)))
Next i
End With
End Sub

snb
07-05-2016, 10:20 PM
Sub M_snb()
For each it in Cells.specialcells(2,2)
st= Split(it, vblf)
Sheets(2).Cells(rows.count, 1).end(xlup).offset(1).Resize(, ubound(st)+1) = st
Next
End Sub

jolivanes
07-05-2016, 11:14 PM
Re:
ubound(sn)
Slip of the finger?

ubound(st)

snb
07-06-2016, 02:00 AM
@joli

No...., of the mind. ;)

jolivanes
07-06-2016, 09:15 AM
@snb
I would never dare to suggest anything like that.