VBA : Remove Number, Extra Spaces, Mark (.) from a Text

[muhammad susanto]

hi all.

i need vba code to remove series number from front text, remove extra spaces, mark dot (.)
i want the code work in any sheet name, cause i want to make code as personal macro/excel add-ins
the code work step:
- select range/cell then click/run macro
here this sample

before run macro	after run macro
1. John We	John We
2. Sisca Madame	Sisca Madame
12.Frans Bonjour	Frans Bonjour
124. Salsa Micro Gav	Salsa Micro Gav

anyone would help me, greatly appreciated..

susanto

 

[DacEasy]

Hi, the code below should work:

Sub forSplitOnPeriod()
  Dim cell As Range
  
  On Error Resume Next
  
  For Each cell In Selection
    cell.Value = Trim(Split(cell.Value, ".")(1)) 'Split on period and remove lead space
  Next cell
End Sub

 

[Siddharth Rout]

hi all.

i need vba code to remove series number from front text, remove extra spaces, mark dot (.)
i want the code work in any sheet name, cause i want to make code as personal macro/excel add-ins
the code work step:
- select range/cell then click/run macro
here this sample

before run macro	after run macro
1. John We	John We
2. Sisca Madame	Sisca Madame
12.Frans Bonjour	Frans Bonjour
124. Salsa Micro Gav	Salsa Micro Gav

anyone would help me, greatly appreciated..

susanto

Why VBA if I may ask? A simple Excel formula can achieve what you want.

=TRIM(MID(A1,FIND(".",A1)+1,256))

If you still want VBA then you can use the above formula in VBA to do the range conversion in ONE GO (No loops required)

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify the range
        Set rng = Range("A1:A" & lRow)
        
        '~~> Update the entire range in 1 GO!
        rng = Evaluate("INDEX(TRIM(MID(" & _
              rng.Address & _
              ",FIND("".""," & _
              rng.Address & _
              ")+1,255)),)")
    End With
End Sub

 

[JEC]

Or like

Sub jec()
 With Range("A2", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("trim(mid(##,search(""."",##)+1,len(##)))", "##", .Address))
 End With
End Sub

 

[DacEasy]

So many choices. Very nice.

 

[Peter_SSs]

Similar to @JEC & @Siddharth Rout but less functions required.

Sub Tidy()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("trim(replace(#,1,find(""."",#),""""))", "#", .Address))
  End With
End Sub

@Siddharth Rout
Your code will give unusual results if Sheet1 is not the active sheet. You have qualified lRow to ws but not rng. Therefore rng will be on the active sheet, not necessarily Sheet1, yet lRow will be determined from Sheet1.

@muhammad susanto
Note that the codes in posts 3, 4 & 5 may not be suitable if you have any data in the columns that does not contain a "."

 

[Siddharth Rout]

Similar to @JEC & @Siddharth Rout but less functions required.

Sub Tidy()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("trim(replace(#,1,find(""."",#),""""))", "#", .Address))
  End With
End Sub

@Siddharth Rout
Your code will give unusual results if Sheet1 is not the active sheet. You have qualified lRow to ws but not rng. Therefore rng will be on the active sheet, not necessarily Sheet1, yet lRow will be determined from Sheet1.

@muhammad susanto
Note that the codes in posts 3, 4 & 5 may not be suitable if you have any data in the columns that does not contain a "."

Yes you are right. I should have qualified the rng object.

Set rng = Range("A1:A" & lRow)

Should have been

Set rng = .Range("A1:A" & lRow)

 

[Peter_SSs]

Should have been

Set rng = .Range("A1:A" & lRow)

Even with that correction, it won't work if Sheet1 is not the active sheet - without further amendment.
I'm not being critical about that since my code (& the others) also only works on the active sheet - just pointing it out in case you thought setting the worksheet like you did would be sufficient to work whether active or not.

 

[Siddharth Rout]

Even with that correction, it won't work if Sheet1 is not the active sheet - without further amendment.
I'm not being critical about that since my code (& the others) also only works on the active sheet - just pointing it out in case you thought setting the worksheet like you did would be sufficient to work whether active or not.

Peter, yes you are right. I should have tested it before posting.

Here is the right way to do it

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
   
    'MsgBox ActiveSheet.Name
   
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
   
    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
       
        '~~> Identify the range
        Set rng = .Range("A1:A" & lRow)
       
        '~~> Update the entire range in 1 GO!
        rng = Evaluate("INDEX(TRIM(MID('" & _
              .Name & "'!" & rng.Address & _
              ",FIND(""."",'" & _
              .Name & "'!" & rng.Address & _
              ")+1,255)),)")
    End With
End Sub

 

[Peter_SSs]

Peter, It will work if the rng is fully qualified.

Not just qualified like at the bottom of post 7.
If you are using Evaluate on a non-active sheet, you have to specify the address as 'External'