Hi!

I am in need of some VBA functionality in Excel and having no prior
experience at all in VBA, I am posting this here. Perhaps some can help me
out here. Thanks in advance for any attempts to get me started or for any
help you provide on this matter.

I have a list of words in Column A which need to be spilt up. For example if
column A contains:

AccountIsParentInstitutionAccount
AccountIsPatient
AccountIsSuspenseAccount
AdjustmentQuantity
AdjustmentReasonCode


The code should iterate through column A and take the word in a cell and
split it up. It should then insert that word in Column B. To make things
clearer, by using the above words as an example, column B should contain:

Account
Is
Parent
Institution
Patient
Suspense
Adjustment
Quantity
Reason
Code



A word doesnt need to be inserted again if it exists already. That is, if a
word exits, then skip it.

Thanks,
Anupam

Hello,

I'll have a look at this soon, will a new word always start with a capital
letter?, will the first character of a new word always be an alpha caharacter
or will it ever be a number.
If you could tell me what it is that you are really trying to do it may save
a lot of time and effort. (Give us an overall picture of what you would like
to do).

Chas

"spartacus13210" wrote:

Hi!

I am in need of some VBA functionality in Excel and having no prior
experience at all in VBA, I am posting this here. Perhaps some can help me
out here. Thanks in advance for any attempts to get me started or for any
help you provide on this matter.

I have a list of words in Column A which need to be spilt up. For example if
column A contains:

AccountIsParentInstitutionAccount
AccountIsPatient
AccountIsSuspenseAccount
AdjustmentQuantity
AdjustmentReasonCode


The code should iterate through column A and take the word in a cell and
split it up. It should then insert that word in Column B. To make things
clearer, by using the above words as an example, column B should contain:

Account
Is
Parent
Institution
Patient
Suspense
Adjustment
Quantity
Reason
Code



A word doesnt need to be inserted again if it exists already. That is, if a
word exits, then skip it.

Thanks,
Anupam

Hi Chas,
Firstly, thanks for taking the time out to look at my problem. Secondly, to
answer your question:

Yes, a new word will always start with a capital letter and will not start
with a number--but, it might end with a number. However, there might be
instances (which i forgot to include in my original post), where a word might
be like--"PatientAlternateIDDBKey." In such a case, i hope that the code can
break down "PatientAlternateIDDBKey" as:
Patient
Alternate
IDDB
Key

Also, there might be a case where the word might be
"HEALTH_REGISTRATION_ID," in such a case i hope the code can insert this word
"as is." Such a word need not be split up.

Thanks in advance for the effort.

"ChasAA" wrote:

Hello,

I'll have a look at this soon, will a new word always start with a capital
letter?, will the first character of a new word always be an alpha caharacter
or will it ever be a number.
If you could tell me what it is that you are really trying to do it may save
a lot of time and effort. (Give us an overall picture of what you would like
to do).

Chas

"spartacus13210" wrote:

Hi!

I am in need of some VBA functionality in Excel and having no prior
experience at all in VBA, I am posting this here. Perhaps some can help me
out here. Thanks in advance for any attempts to get me started or for any
help you provide on this matter.

I have a list of words in Column A which need to be spilt up. For example if
column A contains:

AccountIsParentInstitutionAccount
AccountIsPatient
AccountIsSuspenseAccount
AdjustmentQuantity
AdjustmentReasonCode


The code should iterate through column A and take the word in a cell and
split it up. It should then insert that word in Column B. To make things
clearer, by using the above words as an example, column B should contain:

Account
Is
Parent
Institution
Patient
Suspense
Adjustment
Quantity
Reason
Code



A word doesnt need to be inserted again if it exists already. That is, if a
word exits, then skip it.

Thanks,
Anupam

Hello,
I tried to comment the code a smuch as possible but was in a bit of hurry.
In the end I decided to insert a "$" at each point where ther was a word
break. (If you will be using $ in your initial cell values then just change
the code.

After having added theses $ signs then process the cell value into an array.

repeat this for the remainder of the cells.

Any questions please ask.

Have not tested it to the extent but am confident it will meet your needs.
Insert the code below in a CodeModule

[code]

Function isCapital(tempLetter As String)
' function to see if current char is a capital
Dim CapLetter As Boolean
CapLetter = False
If Asc(tempLetter) 64 And Asc(tempLetter) < 91 Then
CapLetter = True
End If
isCapital = CapLetter
End Function

' ---------------------------------------------------------

Sub SplitWords()
Dim isCap As Boolean
Dim addWord As Boolean
Dim newList()
Dim letPos As Integer
Dim thisChar As String
Dim nextChar As String
Dim prevChar As String
Dim mainWord As String
Dim skip As Boolean
Dim wordCounter As Integer
Dim z As Integer
Dim counter As Integer
Dim wordLen As Integer
Dim off As Integer

Dim leftString As String
Dim rightString As String

ReDim newList(1)

' Select th cell where your data starts
Range("A1").Select
' select all contiguous cell around that cell
Selection.CurrentRegion.Select

wordCounter = 0
' work through each selected cell
For Each cell In Selection
mainWord = cell.Value
mainWord = mainWord & "X" ' add X to end of main word reason as below
wordLen = Len(mainWord)
letPos = 2 'start from 2 because we need to refer to previous
cell
Do While letPos < wordLen
skip = False
' read this , previous and next charcaters
thisChar = Mid(mainWord, letPos, 1)
nextChar = Mid(mainWord, letPos + 1, 1)
prevChar = Mid(mainWord, letPos - 1, 1)
leftString = Left(mainWord, letPos)
rightString = Mid(mainWord, letPos + 1)

' if this char is a "_" just skip to next char
If thisChar = "_" Then
skip = True
End If

' only if this char < "_"
' make a few comparisons
If skip = False Then
If Not isCapital(thisChar) And isCapital(nextChar) Then
mainWord = leftString & "$" & rightString
letPos = letPos + 1
End If
If isCapital(prevChar) And isCapital(thisChar) Then
If Not isCapital(nextChar) And nextChar < "_" Then
leftString = Left(mainWord, letPos - 1)
rightString = Mid(mainWord, letPos)
mainWord = leftString & "$" & rightString
letPos = letPos + 1
End If
End If
End If

' beacuse we have added to the mainword ie $ sign to
' mark end of word, need to cal length of main word again
wordLen = Len(mainWord)

letPos = letPos + 1
Loop

mainWord = Left(mainWord, Len(mainWord) - 1)

' look for end of word and place into array

Do While Len(mainWord) 1
z = InStr(mainWord, "$")
If z 0 Then
thisWord = Left(mainWord, z - 1)
mainWord = Mid(mainWord, z + 1)
End If
addWord = True
For counter = 1 To UBound(newList) ' up to the highes element number of
array
If newList(counter) = thisWord Then
addWord = False 'if current word already in array then ignore it
End If
Next
If addWord And thisWord < "" Then
' if not already in array
' then resize the array and add word
wordCounter = wordCounter + 1
ReDim Preserve newList(wordCounter)
newList(wordCounter) = thisWord
End If
Loop


Next cell
Stop
' now the words are in the array, write them to spreadsheet
' I have chosen cell A13. You can change to whatever you need

Range("A13").Select
' select cells equalling the number of elements in array
off = 1
Do While off <= UBound(newList)
Selection.Offset(off, 0) = newList(off)
off = off + 1
Loop

End Sub

[Code Ends]

Cheers
Chas



"spartacus13210" wrote:

Hi Chas,
Firstly, thanks for taking the time out to look at my problem. Secondly, to
answer your question:

Yes, a new word will always start with a capital letter and will not start
with a number--but, it might end with a number. However, there might be
instances (which i forgot to include in my original post), where a word might
be like--"PatientAlternateIDDBKey." In such a case, i hope that the code can
break down "PatientAlternateIDDBKey" as:
Patient
Alternate
IDDB
Key

Also, there might be a case where the word might be
"HEALTH_REGISTRATION_ID," in such a case i hope the code can insert this word
"as is." Such a word need not be split up.

Thanks in advance for the effort.

"ChasAA" wrote:

Hello,

I'll have a look at this soon, will a new word always start with a capital
letter?, will the first character of a new word always be an alpha caharacter
or will it ever be a number.
If you could tell me what it is that you are really trying to do it may save
a lot of time and effort. (Give us an overall picture of what you would like
to do).

Chas

"spartacus13210" wrote:

Hi!

I am in need of some VBA functionality in Excel and having no prior
experience at all in VBA, I am posting this here. Perhaps some can help me
out here. Thanks in advance for any attempts to get me started or for any
help you provide on this matter.

I have a list of words in Column A which need to be spilt up. For example if
column A contains:

AccountIsParentInstitutionAccount
AccountIsPatient
AccountIsSuspenseAccount
AdjustmentQuantity
AdjustmentReasonCode


The code should iterate through column A and take the word in a cell and
split it up. It should then insert that word in Column B. To make things
clearer, by using the above words as an example, column B should contain:

Account
Is
Parent
Institution
Patient
Suspense
Adjustment
Quantity
Reason
Code



A word doesnt need to be inserted again if it exists already. That is, if a
word exits, then skip it.

Thanks,
Anupam

Thanks so much for your effort and time. I appreciate the help you extended
towards me throught his forum. Good Luck!


"ChasAA" wrote:

Hello,
I tried to comment the code a smuch as possible but was in a bit of hurry.
In the end I decided to insert a "$" at each point where ther was a word
break. (If you will be using $ in your initial cell values then just change
the code.

After having added theses $ signs then process the cell value into an array.

repeat this for the remainder of the cells.

Any questions please ask.

Have not tested it to the extent but am confident it will meet your needs.
Insert the code below in a CodeModule

[code]

Function isCapital(tempLetter As String)
' function to see if current char is a capital
Dim CapLetter As Boolean
CapLetter = False
If Asc(tempLetter) 64 And Asc(tempLetter) < 91 Then
CapLetter = True
End If
isCapital = CapLetter
End Function

' ---------------------------------------------------------

Sub SplitWords()
Dim isCap As Boolean
Dim addWord As Boolean
Dim newList()
Dim letPos As Integer
Dim thisChar As String
Dim nextChar As String
Dim prevChar As String
Dim mainWord As String
Dim skip As Boolean
Dim wordCounter As Integer
Dim z As Integer
Dim counter As Integer
Dim wordLen As Integer
Dim off As Integer

Dim leftString As String
Dim rightString As String

ReDim newList(1)

' Select th cell where your data starts
Range("A1").Select
' select all contiguous cell around that cell
Selection.CurrentRegion.Select

wordCounter = 0
' work through each selected cell
For Each cell In Selection
mainWord = cell.Value
mainWord = mainWord & "X" ' add X to end of main word reason as below
wordLen = Len(mainWord)
letPos = 2 'start from 2 because we need to refer to previous
cell
Do While letPos < wordLen
skip = False
' read this , previous and next charcaters
thisChar = Mid(mainWord, letPos, 1)
nextChar = Mid(mainWord, letPos + 1, 1)
prevChar = Mid(mainWord, letPos - 1, 1)
leftString = Left(mainWord, letPos)
rightString = Mid(mainWord, letPos + 1)

' if this char is a "_" just skip to next char
If thisChar = "_" Then
skip = True
End If

' only if this char < "_"
' make a few comparisons
If skip = False Then
If Not isCapital(thisChar) And isCapital(nextChar) Then
mainWord = leftString & "$" & rightString
letPos = letPos + 1
End If
If isCapital(prevChar) And isCapital(thisChar) Then
If Not isCapital(nextChar) And nextChar < "_" Then
leftString = Left(mainWord, letPos - 1)
rightString = Mid(mainWord, letPos)
mainWord = leftString & "$" & rightString
letPos = letPos + 1
End If
End If
End If

' beacuse we have added to the mainword ie $ sign to
' mark end of word, need to cal length of main word again
wordLen = Len(mainWord)

letPos = letPos + 1
Loop

mainWord = Left(mainWord, Len(mainWord) - 1)

' look for end of word and place into array

Do While Len(mainWord) 1
z = InStr(mainWord, "$")
If z 0 Then
thisWord = Left(mainWord, z - 1)
mainWord = Mid(mainWord, z + 1)
End If
addWord = True
For counter = 1 To UBound(newList) ' up to the highes element number of
array
If newList(counter) = thisWord Then
addWord = False 'if current word already in array then ignore it
End If
Next
If addWord And thisWord < "" Then
' if not already in array
' then resize the array and add word
wordCounter = wordCounter + 1
ReDim Preserve newList(wordCounter)
newList(wordCounter) = thisWord
End If
Loop


Next cell
Stop
' now the words are in the array, write them to spreadsheet
' I have chosen cell A13. You can change to whatever you need

Range("A13").Select
' select cells equalling the number of elements in array
off = 1
Do While off <= UBound(newList)
Selection.Offset(off, 0) = newList(off)
off = off + 1
Loop

End Sub

[Code Ends]

Cheers
Chas



"spartacus13210" wrote:

Hi Chas,
Firstly, thanks for taking the time out to look at my problem. Secondly, to
answer your question:

Yes, a new word will always start with a capital letter and will not start
with a number--but, it might end with a number. However, there might be
instances (which i forgot to include in my original post), where a word might
be like--"PatientAlternateIDDBKey." In such a case, i hope that the code can
break down "PatientAlternateIDDBKey" as:
Patient
Alternate
IDDB
Key

Also, there might be a case where the word might be
"HEALTH_REGISTRATION_ID," in such a case i hope the code can insert this word
"as is." Such a word need not be split up.

Thanks in advance for the effort.

"ChasAA" wrote:

Hello,

I'll have a look at this soon, will a new word always start with a capital
letter?, will the first character of a new word always be an alpha caharacter
or will it ever be a number.
If you could tell me what it is that you are really trying to do it may save
a lot of time and effort. (Give us an overall picture of what you would like
to do).

Chas

"spartacus13210" wrote:

Hi!

I am in need of some VBA functionality in Excel and having no prior
experience at all in VBA, I am posting this here. Perhaps some can help me
out here. Thanks in advance for any attempts to get me started or for any
help you provide on this matter.

I have a list of words in Column A which need to be spilt up. For example if
column A contains:

AccountIsParentInstitutionAccount
AccountIsPatient
AccountIsSuspenseAccount
AdjustmentQuantity
AdjustmentReasonCode


The code should iterate through column A and take the word in a cell and
split it up. It should then insert that word in Column B. To make things
clearer, by using the above words as an example, column B should contain:

Account
Is
Parent
Institution
Patient
Suspense
Adjustment
Quantity
Reason
Code



A word doesnt need to be inserted again if it exists already. That is, if a
word exits, then skip it.

Thanks,
Anupam

€žspartacus13210" napÃ*sal (napÃ*sala):

Hi!

I am in need of some VBA functionality in Excel and having no prior
experience at all in VBA, I am posting this here. Perhaps some can help me
out here. Thanks in advance for any attempts to get me started or for any
help you provide on this matter.

I have a list of words in Column A which need to be spilt up. For example if
column A contains:

AccountIsParentInstitutionAccount
AccountIsPatient
AccountIsSuspenseAccount
AdjustmentQuantity
AdjustmentReasonCode


The code should iterate through column A and take the word in a cell and
split it up. It should then insert that word in Column B. To make things
clearer, by using the above words as an example, column B should contain:

Account
Is
Parent
Institution
Patient
Suspense
Adjustment
Quantity
Reason
Code



A word doesnt need to be inserted again if it exists already. That is, if a
word exits, then skip it.

Thanks,
Anupam

Hi all,
I have similary problem, but i need the words split up according to
gramatical rules (also with division sign) in the same cell. Is anyone able
to help me ?

Many thanks in advance from

Korund

On Wed, 15 Aug 2007 02:38:01 -0700, korund
wrote:

Hi all,
I have similary problem, but i need the words split up according to
gramatical rules (also with division sign) in the same cell. Is anyone able
to help me ?

Many thanks in advance from

Korund


Post detail
--ron

€žRon Rosenfeld" napÃ*sal (napÃ*sala):

On Wed, 15 Aug 2007 02:38:01 -0700, korund
wrote:

Hi all,
I have similary problem, but i need the words split up according to
gramatical rules (also with division sign) in the same cell. Is anyone able
to help me ?

Many thanks in advance from

Korund


Post detail
--ron

e.g. I have in 1 cell txt:
----------------------------
akcionári; valné !
zhromaždenie; BCPB; !
audÃ*tor !
-----------------------------

and after division it could be as:
-----------------------------
akcionári; valné zhro- !
maždenie; BCPB; audÃ*tor!
--------------------------- !

it means that I saved 1 line. I have a lot of similar cells in 1 large sheet
and by printing it cold be less pages (paper saving, ...).
Sorry, it is in Slovak language but I hope, you can now the problem more
understand.

Thank you for your kindness

Korund

On Thu, 16 Aug 2007 17:59:12 -0700, korund
wrote:



„Ron Rosenfeld" napísal (napísala):

On Wed, 15 Aug 2007 02:38:01 -0700, korund
wrote:

Hi all,
I have similary problem, but i need the words split up according to
gramatical rules (also with division sign) in the same cell. Is anyone able
to help me ?

Many thanks in advance from

Korund


Post detail
--ron

e.g. I have in 1 cell txt:
----------------------------
akcionári; valné !
zhromaždenie; BCPB; !
audítor !
-----------------------------

and after division it could be as:
-----------------------------
akcionári; valné zhro- !
maždenie; BCPB; audítor!
--------------------------- !

it means that I saved 1 line. I have a lot of similar cells in 1 large sheet
and by printing it cold be less pages (paper saving, ...).
Sorry, it is in Slovak language but I hope, you can now the problem more
understand.

Thank you for your kindness

Korund

I assume in the above that the "!" represent the left margin of the cell, and
are not an included character.

So far as I know, Excel does not have a method for hyphenation.

You could remove the line feeds and then select the wrap text option
(Format/Cells/Alignment) but this would not give you the hyphenation breaks.




--ron