Remove numbers from cell

Subs · June 7th, 2010, 04:21 AM

Quote:

Originally Posted by Shasur

Hi

You can use the combination of Mid, Instr and Left Functions

Code:

Sub Extract_Numbers_In_Bracs()

Dim oCell As Range
Dim Sval

For Each oCell In Range("A1:A5")
    If InStr(oCell.Value, "(") And InStr(oCell.Value, "(") Then
        Sval = Mid(oCell.Value, InStr(oCell.Value, "(") + 1, Len(oCell.Value))
        oCell.Value = Left(Sval, InStr(Sval, ")") - 1)
    End If
Next oCell

End Sub

Cheers
Shasur

Awesome Thanks so much that worked perfectly!!

Etanobrac · July 6th, 2010, 03:15 PM

Good Afternoon, i was attempting to do something similar to the OP except i needed to remove all text from the end of a cell and keep the numbers at the beginning (for example formatted from 11725 COMPOUND TECHNOLOGY INC. to just 11725). I modified the code given to him that worked but i continue to get the 'Invalid procedure call or argument' and am not exactly sure why. Any help would be much appreciated. Thanks.

Code:

Sub Text_Removal()
    Dim cellText As String
    Dim i As Integer

    For i = 1 To 980
        Range("A" & i).Select
        cellText = Selection.Text

        Do While IsNumeric(Right(cellText, 1)) = False
            cellText = Left(cellText, Len(cellText) - 1)
        Loop
        Selection.Value = cellText
    Next

End Sub

Shasur · July 15th, 2010, 07:45 AM

Hi

This error might occur if the Cell is empty

Here is the modified version for that:

Code:

Sub Text_Removal_2()
    Dim cellText As String
    Dim i As Integer
    For i = 1 To 980
        Range("A" & i).Select
        cellText = Trim(Selection.Text)
        
        If Len(cellText) <> 0 Then
            Do While IsNumeric(Right(cellText, 1)) = False
                cellText = Left(cellText, Len(cellText) - 1)
            Loop
            
            Selection.Value = cellText
        
        End If
    Next
End Sub

Cheers
Shasur

Dark_Signal · November 9th, 2012, 07:27 PM

Hey everyone,

I was wondering if anyone knows how to remove numbers and letters from a column of cells.

I got about 4000 cells that have a pre fix of a seven digit number followed by the letters âLPHâ then a space followed by an eight digit number after that.

Example:
1000100LPH 00005555
1000101LPH 00005556
1000103LPH 00005557
1000104LPH 00005558

All I need is the eight digit number after the âLPH,â the seven digits number and the letters are of no use and I need to delete them all. Is there a way to delete all those unwanted digits and letters without having to do it manually, one by one?
Iâm currently working on Excel2007 and Iâm definitely a noob to this so I wonât be offended if you explain it to me as if I were a third grader.

Any help would be greatly appreciated.

Thank you.

Shasur · November 16th, 2012, 10:15 PM

Hi

This can be done in VBA but here is a simple formula that can be used to do that

=MID(A1,FIND("LPH ",A1)+4,LEN(A1))

Assumes the entire text (1000100LPH 00005555) is in Range A1

Cheers
Shasur

Dark_Signal · November 17th, 2012, 02:17 PM

Quote:

Originally Posted by Shasur

Hi

This can be done in VBA but here is a simple formula that can be used to do that

=MID(A1,FIND("LPH ",A1)+4,LEN(A1))

Assumes the entire text (1000100LPH 00005555) is in Range A1

Cheers
Shasur

Thank you so much Shasur. it worked like a charm.

fabema · September 14th, 2013, 12:49 PM

Hello,

I have column A with 3021 rows, where there are numbers and text like this: 26,33,Cosmetice/Ingrijire Piele,62

I need to delete the numbers and the comma, and keep the text.

Is that possible?

Thank you

stanleydgromjr · September 14th, 2013, 03:00 PM

fabema,

Welcome to the PtoP WROX forum.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

 
Option Explicit
Sub RemoveNumbersCommas()
' stanleydgromjr, 09/14/2013
' Remove numbers from cell
Dim c As Range, a, i As Long
Application.ScreenUpdating = False
a = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", ",")
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c <> "" Then
    For i = LBound(a) To UBound(a)
      c = Replace(c, a(i), "")
    Next i
  End If
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveNumbersCommas macro.

sakshi · November 21st, 2013, 06:21 AM

Can you please suggest me a simple formula to edit this type of cell
1. ABC
2. XYZ
and so on

I need to keep only
ABC
XYZ

Please respond

stanleydgromjr · November 21st, 2013, 06:50 AM

sakshi,

Welcome to the PtoP WROX forum.

If A1 contains:
1. ABC

In B1 enter the following formula, and, copy it down.
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

B1 would display:
ABC

For a macro solution that will replace all the cells in column A with just the text to the right of the space character, try:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

 
Option Explicit
Sub ExtractText()
' stanleydgromjr, 11/21/2013
' Remove numbers from cell
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c = Right(c, Len(c) - WorksheetFunction.Search(" ", c))
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractText macro.

Have a great day,

stanleydgromjr