deleting text

  • Thread starter Thread starter dstiefe
  • Start date Start date
D

dstiefe

I have values in a cell

A3452.79%

I need to delete the first 5 characters from the cell.

how do i do that?

Thank you
 
Depends what you are trying to achieve. Enter a value in cell A2. In another
cell


a) If you only ever want the last 4 characters:

=Left(A2,4)

b) if the value varies in length and you always want to drop the first five
characters:

=RIGHT(A2,LEN(A2)-5)

c) if you want to split at the decimal:

=RIGHT(A2,(LEN(A2)-FIND(".",A2)+1))


The same principal applies in VBA. Just assign a value to a variable,
replace references to the required cell (shown as A2 above) with the
variable.

When you finish, if you need to get rid of the original values:

1) Highlight the range with your formula.
2) Copy.
3) Move where you want your values.
4) Paste special/Values.
5) Delete superfluous data
 
I have values in a cell

A3452.79%

I need to delete the first 5 characters from the cell.

how do i do that?

Thank you
Click to expand...

The Replace *worksheet function* does that. Since you posted in a programming
group, I will suggest a VBA macro.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select a range to process.

Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

================================
Sub Rem5()
Dim c As Range, Rng As Range
Set Rng = Selection 'or whatever

For Each c In Rng
If Len(c.Text) > 5 Then
c.Value = _
WorksheetFunction.Replace(c.Text, 1, 5, "")
End If
Next c
End Sub
================================
--ron
 
c.Value = _
WorksheetFunction.Replace(c.Text, 1, 5, "")
Click to expand...

You can also use VB's built in Mid function to do that as well...

c.Value = Mid(c.Text, 6)

where the 6 is one more than the number of beginning characters that are to
be removed.
 
Back
Top