PDA

View Full Version : un joining text in cells



austenr
01-17-2013, 08:03 AM
I want to do the opposite of what is attached. How do you do it? Thanks

mikerickson
01-17-2013, 08:16 AM
How do you want to do it. If you are working in VBA, it looks like what you have is Join(someArray, vbcr) and you are looking for Split(someString, vbCr).

Or are you looking for worksheet formulas that will do the same as the VB function Split. Like this

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(CHR(13)&A1, CHR(13),REPT(" ",255),2), CHR(13), REPT(" ",255),1),255,255))

Note that the 2 and 1 arguments need to be changed to get the other than first substrings.

austenr
01-17-2013, 08:22 AM
It was from a thread I found on the net but cant find it again to show the code. It uses a join function. I need a VBA solution since there are hundreds of cells like this and it would be very time consuming to do it with formulas. Thanks for the formula though.

mikerickson
01-17-2013, 08:46 AM
Function mySplit(aString As String, Optional Delimiter As String=" ", Optional Indexer As Long = 1)
MySplit = Split(aString & String(Indexer,Delimiter), Delimiter)(Indexer - 1)
End Function

BrianMH
01-17-2013, 08:48 AM
How are we defining the cells you want splitting. Where do you want them to be split to? Are you wanting to split based on the carriage return?

Kenneth Hobs
01-17-2013, 09:02 AM
A UDF going the other way would need a FormulaArray but I am not fond of that as you would need to count and then select the correct number of cells for the result.

Maybe this would suffice though there are shorter code methods to do the same thing.
Sub Test_SplitByVBLFtoColCells()
SplitByVBLFtoColCells Range("A1"), Range("B1")
End Sub

Sub SplitByVBLFtoColCells(sRange As Range, tRange As Range)
Dim v As Variant, c As Range
Set c = tRange
For Each v In Split(sRange, vbLf)
c.Value = v
Set c = c.Offset(1)
Next v
End Sub

austenr
01-17-2013, 09:10 AM
Im uploading what I want the sheet to look like.

In Essence, Column B contains the master row. Column C may or may not have several cells in a word wrapped cell that will need to be split out into different rows down column C.

For example A1 in the Data tab has 3 corresponding rows. In the example I already split them out by C & P by hand on sheet 1.

The "before" picture is in the Data tab. The way I want it to look is in Sheet 1.

austenr
01-17-2013, 10:01 AM
Really I guess all I would want to do is copy the entry in column B, Split the lines in column C (if it only had 1 line in it its ok), find the next blank line and repeat the process for the rest.

BrianMH
01-17-2013, 10:03 AM
not got office at home. if it isn't solved by then will look tomorrow

BrianMH
01-18-2013, 03:51 AM
Bit busier than expected today. If anyone was going to work on this but was waiting as I said I was going to do it please feel free to have at it. Sorry