Extract first 65 characters to new cell

[mordrid]

Hi, I have been searching the message board but can not appear to find a suitable answer so decided to post asking for some help.

I have a spread sheet which I am having to reformat inorder that it can be used as a direct import into a relational database. My dilema is that some of the cells currently contain text from 10 to 240 characters and the new format has to only have 65 characters in the cell meaning that any cell with more than 65 characters needs splitting into another row i.e. say c3 has 120 characters then c3 will need to split 65 in c3 and 55 in d3.

Can anyone please help by providing me a bit of code or formula that can look at a cell and then split the data across multiple cells (either in helper cells or the next row) I am sure I can manipulate it if I can just work out the split function!!!!

I have 7000 lines plus of data to do this against so any help would be great.

Thanks

 

[lynxbci]

Hi,

Cant you use

=LEFT(A1,65) in column 1
=mid(a1,66,65) in column 2
=mid(A1,131,65) in column 3 etc

Kev

 

[JamesW]

One way could be (not ideal though):

D3 = Left(C3,65)
E3 = Mid(C3,66,65)
F3 = Mid(C3,131,65) etc.

 

[JackDanIce]

Hi

Try:

Sub SlideandDice ()
Application.ScreenUpdating = False
Dim i As Long
Dim Length As Integer
Length = 65
For i = 2 To Range("C" & Rows.Count).End(xlUp).Row
    If Len(Range("C" & i)) > Length Then
        Range("D" & i).Value = Right(Range("C" & i), Len(Range("C" & i)) - Length)
        Range("C" & i).Value = Left(Range("C" & i), Length)
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[mordrid]

Hi Guys, thanks so much for the quick responses, that works for me, its always the simple answer that catches me out!!!!

Thanks again really appreciate it

 

[GTO]

Not well thought out, and I don't know if splitting words is a concern, but maybe...

Excel Workbook
	A	B	C	D
1	Mary had a little lamb, who's fleece was white as snow. And everywhere that Mary went, that lamb was sure to go.	113		0
2	Little Jack Horner Sat in the corner, Eating a Christmas pie; He put in his thumb, And pulled out a plum, And said 'What a good boy am I!'	138	Mary had a little lamb, who's fleece was white as snow. And	60
3		0	everywhere that Mary went, that lamb was sure to go.	52
4		0	Little Jack Horner Sat in the corner, Eating a Christmas pie;	61
5		0	He put in his thumb, And pulled out a plum, And said 'What a	60
6			good boy am I!'	15
7				0
8				0
9				0
10				0
Sheet3

Excel 2003

Cell Formulas
Range	Formula
B1	=LEN(A1)
B2	=LEN(A2)
B3	=LEN(A3)
B4	=LEN(A4)
B5	=LEN(A5)
D1	=LEN(C1)
D2	=LEN(C2)
D3	=LEN(C3)
D4	=LEN(C4)
D5	=LEN(C5)
D6	=LEN(C6)
D7	=LEN(C7)
D8	=LEN(C8)
D9	=LEN(C9)
D10	=LEN(C10)

Sub exa4()
Dim Cell As Range
Dim sText As String
Dim sTmp As String
Dim bolValid As Boolean
    
    With Sheet3
        For Each Cell In Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
            If Not Len(Cell.Text) > 65 Then
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = Cell.Value
            Else
                sText = Cell.Text
                
                Do While Len(sText) > 0
                    If Len(sText)<= 65 Then
                        .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = Trim(sText)
                        Exit Do
                    End If
                    
                    sTmp = Left(sText, 65)
                    Do
                        sTmp = Left(sTmp, Len(sTmp) - 1)
                        If Right(sTmp, 1) = Chr(32) Then
                            bolValid = True
                            Exit Do
                        End If
                    Loop While Len(sTmp) > 0 And Not Right(sTmp, 1) = Chr(32)
                    If bolValid Then
                        .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = Trim(sTmp)
                        bolValid = False
                        sText = Mid(sText, Len(sTmp))
                        sTmp = vbNullString
                    Else
                        MsgBox "ACK!  I goobered something"
                    End If
                Loop
            End If
        Next
    End With
                        
End Sub

 

[mordrid]

Hi GTO, thanks for your suggestions I will take a look and have a play later. As for splitting words is not an issue as the important thing is 65 Chs max as otherwise the db will throw it out. Once in the db it all ends up back together when reporting etc is done so no worries there.

Its great to see the different ideas to solve the same problem, this is certainly giving me good ideas of how to move forward

Thanks again

 

[moralmildred]

Hi,

GTO's code is good but i need to make some changes... Can someone help me how to add a separator to the result on column C

the output should be like this.. see sample:

Thanks