Dividing long string in 1 cell to 2 cells

[newvbie]

Hi there. I got 90,000 records in which contents may exceed the required length for each cell. Here is the example:

A2: UNIT A 14/F JCG INTERNATIONAL BUILDING 16 MONGKOK ROAD HONG KONG

I tried the following vba to break the cell into two but the word will be truncated.

For k = 2 To 95000
leng = Len(Range("A" & k))
If leng > 30 Then //if the lengh > 30 characters
Range("B" & k).Value = Right(Range("A" & k), 20)
Range("A" & k).Value = Left(Range("A" & k), leng - 20)
End If
Next

How can capture (by simple vba script) the required length of cell A2 with the space at the end and transfer the remaining part to cell B2 to the following effect?

A2: UNIT A 14/F JCG INTERNATIONAL
B2: BUILDING 16 MONGKOK ROAD HONG KONG

Thanks.

 

[Andrew Poulsom]

Maybe this will help you:

http://www.mrexcel.com/forum/showthread.php?t=21156