MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Extract text in a colomn to 5 colomn

  Asked By: Ryan    Date: Jan 15    Category: MS Office    Views: 805

Would like to know how macro could extract the text in a colomn to 5
colomn. However, the text lenght in each colomn should contain no
more than 25 characters including spaces. Otherwise, macro will
notify the user to edit the data.

The content in A1 is Mr Sangeta kaugan PT Lunergaten international
Patra Jasa Building Jl. Gatot subroto Kav.45 Jakarta 12950 Indonesia.

in B1 -> Mr Sangeta Kaugan
In C1 -> PT Lunergaten International "This text length is 28, How
could macro notify to user for edit data", afterwards, macro will
looping for next data in cell D1,
In D1 -> Patra Jasa Building
In E1 -> Jl. Gatot Subroto Kav.45
F1 -> Jakarta 12950 Indonesia



3 Answers Found

Answer #1    Answered By: Jakson Williams     Answered On: Jan 15

Re:"macro will notify the user  to edit  the data"
Relatively easily, see example  code below.

Re:"how macro  could extract  the text  in a colomn to 5 colomn"
As I think you want it; with difficulty. How will the computer know to
split the string between 'Kaugan' and 'PT', when if 'PT' was to be
included in the first split 'Mr Sangeta kaugan PT' is still only 20
characters? If this is done then the next string is NOT too long?!
I think Kaugan is the person's last name, but how will the computer
know this?

Anyway, I've begun to write some code to give you some ideas; it works
on the selected cell(s) and places strings in the 5 (or more) columns
to the right, trying to get as close to 25 characters  in each cell as
it can, assuming the string is to be split using spaces  as delimiter:

Sub blah()
For Each cll In Selection.Cells
p = Split(Application.Trim(cll), " ")
StringNo = 1
i = 0
myStr = ""
myStr = Application.Trim(myStr & " " & p(i))
i = i + 1
Loop Until i >= UBound(p) Or Len(Application.Trim( _
myStr & " " & p(i))) >= 25
If Len(myStr) < 26 Then
cll.Offset(, StringNo) = myStr
result = InputBox("You need to remove " & Len(myStr _
) - 25 & " characters. Do this now", _
"Needs trimming a bit", myStr)
If result <> "" Then myStr = result
Loop Until Len(myStr) < 26
cll.Offset(, StringNo) = myStr
End If
StringNo = StringNo + 1
Loop Until i >= UBound(p)
Next cll
End Sub

Answer #2    Answered By: Abney Martin     Answered On: Jan 15

Another approach would be to create a Change event in the worksheet.
Here, check the Target.Address to see if the cell you're "watching" has
been changed.
if it is, then use "len(target.value)" to check the length of the
If it is over the tested value, use msgbox to display a warning.

If this is the approach you would like, I can post some sample code.

Answer #3    Answered By: Fannie Gonzales     Answered On: Jan 15

Appreciate if you can post the sample code.

Didn't find what you were looking for? Find more on Extract text in a colomn to 5 colomn Or get search suggestion and latest updates.