Insert values on multiple rows per line break

[greenwall]

Hi

I was hoping someone could help with me this as I'm a newbie. I tried searching the forums but I've not had much luck.

I'm currently working on a requirement to get rid of 3 spaces and replace it with a line break which I've managed to do. Now the problem I have is how do I display the values across multiple rows - 1 row for each line break

[CODE=vba]
Sub InsertLineBreak()
Dim cell As Range

    For Each cell In Selection
    'Find non-breaking spaces Chr(160) and convert them to space characters i.e. Chr(32)    
        Do
        cell.Value = Trim(Replace(cell.Value, Chr(160), Chr(32)))
        Loop While cell.Value = Chr(160)
        
        'Once it's converted to the normal space character then a line break Chr(13) can be inserted
        cell.Value = Trim(Replace(cell.Value, "   ", Chr(13)))

       ' Next step -- Need to display values across multiple rows on cells A31:A65 

    Next

End Sub

[/CODE]

 

[Alex Blakenburg]

Can you mock up how you want the output to look and attach a screenshot (or even better an xl2bb shot), and also an image of what it looks like before you start making any changes.
Preferably more than one line because spreading it over 3 rows has me envisioning it overwriting other data.

 

[greenwall]

Can you mock up how you want the output to look and attach a screenshot (or even better an xl2bb shot), and also an image of what it looks like before you start making any changes.
Preferably more than one line because spreading it over 3 rows has me envisioning it overwriting other data.

So basically this is how I would like it to look like the line breaks are displayed across multiple rows

It starts like this below, raw data is pasted on cell A2 (or any cell). I don't mind if it overwrites the values already from column A2 and the rows below it, the sheet will only store 1 candidate details at a time.

 

[Alex Blakenburg]

So basically this is how I would like it to look like the line breaks are displayed across multiple rows

View attachment 33203

It starts like this below, raw data is pasted on cell A2 (or any cell). I don't mind if it overwrites the values already from column A2 and the rows below it, the sheet will only store 1 candidate details at a time.

View attachment 33204

See if this helps. It probably needs some enhancement around setting ColOffset to 0 if you are trying to convert multiple rows / candidates.
I know you said you were only putting in one candidate at a time but your code indicated you were already considering multiple candidates.

Sub InsertLineBreak()

    Dim rngIn As Range
    Dim arrStr() As String
    Dim FldNo As Long
    Dim RowOffset As Long
    Dim RowOut As Long
    Dim ColOffset As Long
    Dim ColOut As Long
    Dim rCnt As Long
       
    Set rngIn = Selection
    
    ' if you only have 1 row and you want to overwrite that data
    ' set both offsets to 0
    RowOffset = 0
    ColOffset = 1
    
    RowOut = rngIn.Row + RowOffset
    ColOut = rngIn.Column + ColOffset
        
    ' Use the Chr(160) to split the row into its components

    ' Loop through no of rows in selection
    For rCnt = 1 To rngIn.Rows.Count
        arrStr = Split(rngIn.Cells(rCnt), Chr(160))
        ' Loop through the number of fields in the input string
        For FldNo = LBound(arrStr) To UBound(arrStr)
            'Remove the leading and trailing spaces and write out
            Cells(RowOut, ColOut) = Trim(arrStr(FldNo))
            RowOut = RowOut + 1
        Next FldNo
    Next rCnt
End Sub

 

[greenwall]

I appreciate the help Alex that worked me, i just had to make some minor tweaks so that the Split happens on the correct position.

Thanks a lot.

 

[Alex Blakenburg]

Glad you were able to modify it to fit. The screenshots you sent helped a lot.
You already did the part that trips most people up which is identifying the non-breaking space chr(160).