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

Try changing cell's Warp Text property to false, manually or with code

Regards,
Peter T

wrote in message
ps.com...
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

soft returns are Chr(10) - vbLf

Sub getRID()

Dim a
Dim txtVal
Dim Rres

txtVal = Sheets(2).Range("f3").Text
For a = 1 To Len(txtVal)
Rres = Mid(txtVal, a, 1)
Debug.Print Rres
If Rres = " " Then Debug.Print "space"
If Rres = vbCr Then Debug.Print "vbCr"
if Rres = vbLf then Debug.Print "VBLf"
Next
End Sub

just to demo from the immediate window:

? Instr(1,activecell,vbLf,vbTextCompare)
4
? activecell.Text
ABC
EFG


--
Regards,
Tom Ogilvy


" wrote:

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

On Aug 2, 10:44 am, Tom Ogilvy
wrote:
soft returns are Chr(10) - vbLf

Sub getRID()

Dim a
Dim txtVal
Dim Rres

txtVal = Sheets(2).Range("f3").Text
For a = 1 To Len(txtVal)
Rres = Mid(txtVal, a, 1)
Debug.Print Rres
If Rres = " " Then Debug.Print "space"
If Rres = vbCr Then Debug.Print "vbCr"
if Rres = vbLf then Debug.Print "VBLf"
Next
End Sub

just to demo from the immediate window:

? Instr(1,activecell,vbLf,vbTextCompare)
4
? activecell.Text
ABC
EFG

--
Regards,
Tom Ogilvy



" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Tom. It was a vbLf.

Here's what I did:

Sub X_SoftReturns()

Dim a As Long
Dim txtVal As String
Dim rowCnt As Long

rowCnt = Range("f2").CurrentRegion.Rows.Count

For a = 2 To rowCnt
txtVal = Range("f" & a).Text
txtVal = Replace(txtVal, vbLf, " ")
Range("f" & a).Value = txtVal
Next

End Sub