Deleting part of a text in a cell in Excel using VB

[direktoren]

Hi,

Just a quick question - is it possible to delete a part of a cells content.

ex.

cell A1 says "345 Text" and I would like to delete 345, so it just states "text"

In my worksheet I have some 50 companies, but in front of the company name there is a 5-digit registration number - it is this number I would like to delete

Is this possible??

[Static]

Say u have a range A1 to A50 that has the names...

Dim tmp As String 
For x = 1 To 50
     tmp = xls.Range("A" & x)
     xls.Range("A" & x) = Trim(Right(tmp,Len(tmp)-5))
Next

[cssriraman]

Say u have a range A1 to A50 that has the names...

Dim tmp As String 
For x = 1 To 50
     tmp = xls.Range("A" & x)
     xls.Range("A" & x) = Trim(Right(tmp,Len(tmp)-5))
Next

Great code!

I would like to make one change on that. If the cell is empty then it will throw the error. So, we need to avoid that error:

Dim x As Integer
Dim tmp As String
For x = 1 To 5
     tmp = Range("A" & x)
     If Not Len(tmp) = 0 Then
     Range("A" & x) = Trim(Right(tmp, Len(tmp) - 5))
     End If
Next

[Static]

Bah.. technicalities!! LOL

good thinkin CS....

[thelocaluk]

How would you make this work, it keeps looping??

Sub Remove()
Dim numnRep As Long
 numRep = "0"

Do While numRep <> ""
  Sheet1.Range("a1", Range("a100").End(xlUp)).replace What:=numRep, Replacement:="", LookAt:=xlValue, _
    SearchOrder:=xlByRows, MatchCase:=False
  numRep = numRep + 1
Loop

End Sub

[cssriraman]

How would you make this work, it keeps looping??

Sub Remove()
Dim numnRep As Long
 numRep = "0"

Do While numRep <> ""
  Sheet1.Range("a1", Range("a100").End(xlUp)).replace What:=numRep, Replacement:="", LookAt:=xlValue, _
    SearchOrder:=xlByRows, MatchCase:=False
  numRep = numRep + 1
Loop

End Sub

Your code has lots of confusion:
1. in the beginning you are declaring numnRep As Long
2. Next you are assigning 0 to numRep (which is not numnRep)
3. in Loop you are incrementing 1 everytime to numRep.

There are some data type issues. You need to check and correct it.

If it is a new thread please post it separately.

[thelocaluk]

It's related to the same thread, to remove the numbers from cells that also contain letters. This code removes the 0's then 1's,2's,ect. Yes i should declare

Dim numnRep As Long

as

Dim numRep As String

, but how do I stop the loop??

[Static]

no u are making it too complicated...

u dont want to use the replace....
if the 1st 5 characters in the cell are the reg number and u want to remove them:

Public Sub Remove(Col As String)
    Dim Row As Integer
    Dim t() As String
    t = Split(ActiveSheet.UsedRange.Address, "$")
    Row = t(UBound(t))
     Dim x As Integer
     Dim tmp As String
     For x = 1 To Row
          tmp = Range(Col & x)
          If Not Len(tmp) = 0 Then
               Range(Col & x) = Trim(Right(tmp, Len(tmp) - 5))
          End If
     Next
End Sub

usage:

Remove "A"

will fix column A
it will find the used range of the sheet.. grab the end row.. then loop through and clip off the first 5 characters...
I would stay away from find replace.. it causes errors in the code if it doesnt find anything

[thelocaluk]

Thank's, I'm learning every day

[direktoren]

As the actual poser of the question, let me give my utmost appreciation - I had never thought of that one myself - didn't even know the trim function...

thanx
/nick