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

 

[Siddharth Rout]

It doesn't for me if the text is on Sheet1 (that is the sheet whose codename is Sheet1 not the tab name) and that sheet is not the active sheet.
If you are using Evaluate on a non-active sheet you need to specify the address in the evaluate function as 'External'

Peter I was already in the process of updating my post . See the updated post.

 

[Peter_SSs]

Peter I was already in the process of updating my post . See the updated post.

I edited mine too. That code does not work for me if the data to be manipulated is on Sheet1 and that sheet is not the active sheet. See my updated last post about specifying the address as external.

 

[Siddharth Rout]

I edited mine too. That code does not work for me if the data to be manipulated is on Sheet1 and that sheet is not the active sheet. See my updated last post about specifying the address as external.

my updated code in #9 will work even if sheet1 is not active.

 

[Peter_SSs]

my updated code in #9 will work even if sheet1 is not active.

I'm sorry, I had two sample workbooks open and had your updated code in the wrong workbook.
Yes, your updated code works fine.

 

[Siddharth Rout]

I'm sorry, I had two sample workbooks open and had your updated code in the wrong workbook.
Yes, your updated code works fine.

Thats ok. I am sure this discussion would help OP. We all make these mistakes. My first mistake (which I rarely do is leave my objects unqualified). My second (Again, this is something which I rarely do) was to put and test the code in Sheet Code Module and not in the regular Module .

Sigh, I guess it is one of those days...

 

[muhammad susanto]

hi guys...thank you but
how if the range is not always in col A, i want the range is dynamic, depend on where is data place
modified the new step:
1. at first, select your range
2. running code/macro

 

[Peter_SSs]

With Range("A2", Range("A" & Rows.Count).End(xlUp))
With Selection

.. and that takes the whole active v non-active sheet discussion out of the question.

 

[DacEasy]

Did you try mine, it is based on selecting a range, then running the code/macro?

 

[muhammad susanto]

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

hi i found new problem, how about if data not consistent use mark (.), sometimes contains mark (.) sometimes not contains mark (.)
like this sample

new case	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

 

[Peter_SSs]

Assuming that you are selecting cells in a single column try this. It removes anything before the first letter.
(If you could be selecting cells in multiple contiguous columns it is easily adaptable)

Sub Tidy_Up()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "^[^A-Za-z]*"
  With Selection
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), "")
    Next i
    .Value = a
  End With
End Sub