Insert values on multiple rows per line break

greenwall

New Member
Joined
Feb 25, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

VBA Code:
[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]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
586
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 25, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

1614435811543.png


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.

Before.JPG
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
586
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 
Solution

greenwall

New Member
Joined
Feb 25, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
586
Office Version
  1. 365
Platform
  1. Windows
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). (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,422
Members
417,025
Latest member
MusterDuster

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
Top