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 14 cm

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

Ballspiele von Glorious/Leue

Volleyball spielerisch Lernen von Papageorgiou/Pabst/Bussmann

Fußball - modernes Nachwuchstraining von Barth/Rutemöller/Zempel

Rückschlagspiele von Weyers/Müller/Lemke

Moderner Angriffsfußball von Elgert/Schreiner

Koordination im Fußball von Buschmann/Bussmann/Pabst

Fußball: Stabilisationstraining von Kollath/Buschmann

Burner Games Burner Motion Band 1 von Muriel Sutter

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>
</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