Divide a long string in a Cell, separated by commas

[Henrik_DK]

Hi

I have long string of data in A1, this string contain letters, numbers and spaces BUT are divided with a comma.
I need for split it into max 800 characters, and the split should be after a comma, so we keep the unique name/number.

It would be helpful, if the new lines would be placed under the original "to long" line, and with a space in between, so first new line is in A3 and second in A5 etc.
The second line should not start with a comma, as that will conflict in the further process

Do anyone have a take on this ?

br Henrik

 

[vladimiratanasiu]

Hi

I have long string of data in A1, this string contain letters, numbers and spaces BUT are divided with a comma.
I need for split it into max 800 characters, and the split should be after a comma, so we keep the unique name/number.

It would be helpful, if the new lines would be placed under the original "to long" line, and with a space in between, so first new line is in A3 and second in A5 etc.
The second line should not start with a comma, as that will conflict in the further process

Do anyone have a take on this ?

View attachment 71763

br Henrik

How should be structured the new string ? Does it keep commas and / or spaces within the 800 characters, or are they removed completly ?

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, the following would help:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


See if this smaller example puts you on the right path.

Sub BreakItUp_Discard_Comma()
  Dim s As String
  Dim k As Long
  Dim result As Variant
 
  Const CharsPerLine As Long = 15     '<-Change to suit (eg 800)
  s = Range("A1").Text
  ReDim result(1 To Len(s), 1 To 1)
  k = 0
  Do Until Len(s) = 0
    k = k + 2
    result(k, 1) = RTrim(Left(s, InStrRev(s & "," & Space(CharsPerLine), ",", CharsPerLine + 1) - 1))
    s = Mid(s, Len(result(k, 1)) + 2)
  Loop
  With Range("A2").Resize(k)
    .NumberFormat = "@"
    .Value = result
  End With
End Sub

My sample data and results.

Henrik_DK.xlsm
	A
1	20391,2373,29232,6052356,PT4553 9.6 CL,123,KK 12.23,456987,AB CD EFG,22,33,44,55,66,77,88,99
2
3	20391,2373
4
5	29232,6052356
6
7	PT4553 9.6 CL
8
9	123,KK 12.23
10
11	456987
12
13	AB CD EFG,22,33
14
15	44,55,66,77,88
16
17	99
Sheet3

 

[Henrik_DK]

Welcome to the MrExcel board!

For the future, the following would help:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


See if this smaller example puts you on the right path.

Sub BreakItUp_Discard_Comma()
  Dim s As String
  Dim k As Long
  Dim result As Variant
 
  Const CharsPerLine As Long = 15     '<-Change to suit (eg 800)
  s = Range("A1").Text
  ReDim result(1 To Len(s), 1 To 1)
  k = 0
  Do Until Len(s) = 0
    k = k + 2
    result(k, 1) = RTrim(Left(s, InStrRev(s & "," & Space(CharsPerLine), ",", CharsPerLine + 1) - 1))
    s = Mid(s, Len(result(k, 1)) + 2)
  Loop
  With Range("A2").Resize(k)
    .NumberFormat = "@"
    .Value = result
  End With
End Sub

My sample data and results.

Henrik_DK.xlsm
	A
1	20391,2373,29232,6052356,PT4553 9.6 CL,123,KK 12.23,456987,AB CD EFG,22,33,44,55,66,77,88,99
2
3	20391,2373
4
5	29232,6052356
6
7	PT4553 9.6 CL
8
9	123,KK 12.23
10
11	456987
12
13	AB CD EFG,22,33
14
15	44,55,66,77,88
16
17	99
Sheet3

Hi Peter - Thanks for your swift reply and perfect solution, I will look into those tools you have mentioned.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.