M
meh2030
The company I work for has a subscription to a database called SDC.
SDC allows you to build queries and then output the results in an
Excel worksheet. One of the fields gives a text description. The
description is output into one single cell in the worksheet; and the
description is roughly 150 words (and there are multiple row records,
so this occurs for many cells). However, an example of the text looks
like the following (pasted directly from the cell):
GERMANY - Novartis AG (NA) of
Switzerland, acquired the
entire share capital of Hexal
.....(more text)....
So, all of the text appears in this columnar structure. I want the
text string to be one continuous line, not a column-like structure.
It doesn't matter how wide I stretch the column, the text doesn't fill
the column. Now, the odd thing is that there is NO space or vbCr in
between the words "of" and "Switzerland" or between the words "the"
and "entire". I have to manually place the mouse into the cell, move
the mouse pointer after the letter "f" in the word "of", hit the
<Delete> key, and then hit the <space-bar>. (And then repeat till
finished).
I'm looking to see if there is a way for VBA to recognize where the
text continues onto the next visible line. For example, even though
the above noted text (starting with "GERMANY") is in one cell, can the
computer recognize that a new line of text begins with the word
"Switzerland"?
I included some code below with what I thought might work, but there
are two problems: (1) "of" [next line] "Switzerland" is read as
"ofSwitzerland" and "the" [next line] "entire" is read as "theentire"
and (2) even when I break the text into an array, move it to another
worksheet, and then smash it back together again, it still looks like
the above text:
GERMANY - Novartis AG (NA) of
Switzerland, acquired the
entire share capital of Hexal
.....(more text)....
Note: If I clear cell formats, the columnar text will fit into a
single line (i.e. you see "GERMANY - Novartis AG (NA) ofSwitzerland,
acquired theentire..."; however, if I select a description cell and
Edit that cell (either <F2> or select the text via the Formula Bar)
the cell appearance returns to the columnar structure (and the row
height is 345). Then, if I change the row height to 12, the only
thing you see is "GERMANY - Novartis AG (NA) of". Lastly, in the
"getRID" sub routine, the VBA will recognize " " but not Chr(13).
It's really odd. Thanks in advance for any ideas.
Matt
Sub getRID3()
Dim a
Dim txtVal
Dim newTxt()
Dim bigStrg
Dim wordCnt
Dim rngCnt
'split it by space and then string it back together again?
txtVal = Range("f3").Value
wordCnt = Split(txtVal, " ")
ReDim newTxt(0 To UBound(wordCnt))
For a = LBound(wordCnt) To UBound(wordCnt)
newTxt(a) = Trim(Split(txtVal, " ")(a))
Debug.Print "newTxt:"; newTxt(a)
Sheets(1).Cells(a + 1, 1).Value = newTxt(a)
Next
rngCnt = Sheets(1).Cells(1, 1).CurrentRegion.Rows.Count
ReDim newTxt(1 To rngCnt)
For a = 1 To UBound(newTxt)
newTxt(a) = Sheets(1).Cells(a, 1).Value
Debug.Print "newTxt:"; newTxt(a)
Next
For a = 1 To UBound(newTxt)
bigStrg = bigStrg & newTxt(a) & " "
Debug.Print "bigStrg:"; bigStrg
Next
Range("f1").Value = bigStrg
Range("f1").Select
End Sub
Sub getRID()
Dim a
Dim txtVal
Dim Rres
txtVal = Sheets(2).Range("f3").Text
For a = 1 To Len(txtVal)
Rres = Right(Left(txtVal, a), 1)
Debug.Print Rres
If Rres = " " Then Debug.Print "space"
If Rres = vbCr Then Debug.Print "vbCr"
Next
End Sub
SDC allows you to build queries and then output the results in an
Excel worksheet. One of the fields gives a text description. The
description is output into one single cell in the worksheet; and the
description is roughly 150 words (and there are multiple row records,
so this occurs for many cells). However, an example of the text looks
like the following (pasted directly from the cell):
GERMANY - Novartis AG (NA) of
Switzerland, acquired the
entire share capital of Hexal
.....(more text)....
So, all of the text appears in this columnar structure. I want the
text string to be one continuous line, not a column-like structure.
It doesn't matter how wide I stretch the column, the text doesn't fill
the column. Now, the odd thing is that there is NO space or vbCr in
between the words "of" and "Switzerland" or between the words "the"
and "entire". I have to manually place the mouse into the cell, move
the mouse pointer after the letter "f" in the word "of", hit the
<Delete> key, and then hit the <space-bar>. (And then repeat till
finished).
I'm looking to see if there is a way for VBA to recognize where the
text continues onto the next visible line. For example, even though
the above noted text (starting with "GERMANY") is in one cell, can the
computer recognize that a new line of text begins with the word
"Switzerland"?
I included some code below with what I thought might work, but there
are two problems: (1) "of" [next line] "Switzerland" is read as
"ofSwitzerland" and "the" [next line] "entire" is read as "theentire"
and (2) even when I break the text into an array, move it to another
worksheet, and then smash it back together again, it still looks like
the above text:
GERMANY - Novartis AG (NA) of
Switzerland, acquired the
entire share capital of Hexal
.....(more text)....
Note: If I clear cell formats, the columnar text will fit into a
single line (i.e. you see "GERMANY - Novartis AG (NA) ofSwitzerland,
acquired theentire..."; however, if I select a description cell and
Edit that cell (either <F2> or select the text via the Formula Bar)
the cell appearance returns to the columnar structure (and the row
height is 345). Then, if I change the row height to 12, the only
thing you see is "GERMANY - Novartis AG (NA) of". Lastly, in the
"getRID" sub routine, the VBA will recognize " " but not Chr(13).
It's really odd. Thanks in advance for any ideas.
Matt
Sub getRID3()
Dim a
Dim txtVal
Dim newTxt()
Dim bigStrg
Dim wordCnt
Dim rngCnt
'split it by space and then string it back together again?
txtVal = Range("f3").Value
wordCnt = Split(txtVal, " ")
ReDim newTxt(0 To UBound(wordCnt))
For a = LBound(wordCnt) To UBound(wordCnt)
newTxt(a) = Trim(Split(txtVal, " ")(a))
Debug.Print "newTxt:"; newTxt(a)
Sheets(1).Cells(a + 1, 1).Value = newTxt(a)
Next
rngCnt = Sheets(1).Cells(1, 1).CurrentRegion.Rows.Count
ReDim newTxt(1 To rngCnt)
For a = 1 To UBound(newTxt)
newTxt(a) = Sheets(1).Cells(a, 1).Value
Debug.Print "newTxt:"; newTxt(a)
Next
For a = 1 To UBound(newTxt)
bigStrg = bigStrg & newTxt(a) & " "
Debug.Print "bigStrg:"; bigStrg
Next
Range("f1").Value = bigStrg
Range("f1").Select
End Sub
Sub getRID()
Dim a
Dim txtVal
Dim Rres
txtVal = Sheets(2).Range("f3").Text
For a = 1 To Len(txtVal)
Rres = Right(Left(txtVal, a), 1)
Debug.Print Rres
If Rres = " " Then Debug.Print "space"
If Rres = vbCr Then Debug.Print "vbCr"
Next
End Sub