Counting characters t0 40 and then split ...

[Morty07]

Hi all!

Is it possible to count characters to for instance 40, then split the cell to a column.

I want to split in hole words.

please, please help.

Morty07

 

[SteveO59L]

Try posting a sample of your data, and what you would like to do with it

 

[Morty07]

Try posting a sample of your data, and what you would like to do with it

I will Steve.
Here are an example of the list.

Champion Cup RIO, sølv størrelse 17 cm

Champion Cup RIO, sølv størrelse 20 cm

Champion Cup RIO, sølv Sett med 3

Champion Cup Rio, gull størrelse 14 cm

Champion Cup Rio, gull størrelse 17 cm

Champion Cup Rio, gull størrelse 20 cm

Champion Cup RIO, gull sett med 3

Informasjon Prospect Badminton

Molten ® Basketball GG6 størrelse 6, FIBA godkjent

Molten ® Basketball FX7 Størrelse 7, FIBA godkjent

<colgroup><col style="mso-width-source:userset;mso-width-alt:18066;width:371pt" width="494"> </colgroup><tbody>
[TD="width: 494"]Champion Cup RIO, sølv størrelse 14 cm[/TD]

[TD="class: xl63"]Ballspiele von Glorious/Leue[/TD]

[TD="class: xl64"]Volleyball spielerisch Lernen von Papageorgiou/Pabst/Bussmann[/TD]

[TD="class: xl65"]Fußball - modernes Nachwuchstraining von Barth/Rutemöller/Zempel[/TD]

[TD="class: xl64"]Rückschlagspiele von Weyers/Müller/Lemke[/TD]

[TD="class: xl65"]Moderner Angriffsfußball von Elgert/Schreiner[/TD]

[TD="class: xl64"]Koordination im Fußball von Buschmann/Bussmann/Pabst[/TD]

[TD="class: xl65"]Fußball: Stabilisationstraining von Kollath/Buschmann[/TD]

[TD="class: xl64"]Burner Games Burner Motion Band 1 von Muriel Sutter[/TD]

</tbody>

This is an example off the text.
The list are being imported to a software, where the description of the products only allows 40 characters.
I want to count 40 characters(including spaces), and split to the next column in hole words.

Hope this explain more.

 

[ukmikeb]

Hi

Try :-

=IF(LEN(A2)<41,A2,LEFT(A2,MIN(40,MAX(ROW(INDIRECT(1&":"&40))*(MID(A2,ROW(INDIRECT(1&":"&40)),1)=" ")))))

entered with Control-Shift-Enter.

hth

 

[Robert Mika]

Sub split()
Dim Ash As Worksheet
Dim r As Range
Dim b As String
Dim LastRow As Long
Set Ash = ActiveSheet
Dim lastoc As String
Dim lastchar As Long
LastRow = Ash.Cells(Rows.Count, "A").End(xlUp).Row
lastoc = " "
Application.ScreenUpdating = False
For Each cell In Ash.Range("A1:A" & LastRow).Cells
   lastchar = InStrRev(cell, lastoc)
   If Len(cell) > 40 Then
    b = Mid(Left(cell, 40), 1, InStrRev(Left(cell, 40), lastoc))
    cell.Offset(0, 1) = Replace(cell, b, "")
    cell.Value = b
  
   End If
 Next cell