remove end of cell marker from string

[stuck]

In Word 2010 VBA if I say:

Code: Select all

cellContent = Selection.Tables(1).Cell(2, colNo).Range.Text
strIn = InputBox("Edit if nec...", "update cell", cellContent)
Selection.Tables(1).Cell(2, colNo).Range.Text = strIn

then the input box I get presents the cell contents including the end of cell marker, which looks like a space followed by a bullet point. How can I get the default text to be just the text without the 'space+bullet point' on the end?

Thanks,

Ken

[HansV]

You could add this line:

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

This will still include an invisible carriage return vbCr at the end. If you want to omit that too:

cellContent = Left(cellContent, Len(cellContent) - 2)

[Rudi]

    
You could also change the line:
Selection.Tables(1).Cell(2, colNo).Range.Text = strIn

...to:
If strIn <> "" Then Selection.Tables(1).Cell(2, colNo).Range.Text = strIn

...in case a person cancels the Inputbox.

[stuck]

Thank you both.

From what I know of Excel VBA I should have been able to figure out that the fix would involve 'Left' and 'Len'.

Ken

[ChrisGreaves]

Hi Ken,
I've had this in my librarty for 8 years:-

Code: Select all

Public Function strCellText(rng As Range) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''' Function:   strCellText
    '''
    ''' Comments:   Return the text contents of a cell range.
    '''
    ''' Arguments:  RANGE           Of a table cell
    '''
    ''' Returns:    STRING
    '''
    ''' Date        Developer       Action
    ''' --------------------------------------------------------------------------
    ''' 2008/11/22  Chris Greaves   Created
    '''
    strCellText = Left$(rng.Text, Len(rng.Text) - 2)
    'Sub TESTstrCellText()
    '    MsgBox strCellText(ActiveDocument.Tables(1).Rows(1).Cells(1).Range)
    'End Sub
End Function

[macropod]

If the cell contains only a single paragraph, you could use:
cellContent = Split(Selection.Tables(1).Cell(2, colNo).Range.Text, vbCr)(0)