REMOVE BULLETS/NUMBERING FROM TEXT

[VDS]

@ Dear All,

I have the following data :-

1. We have to register one Mobile No
2. Whenever we place Order for supply of material
3. The one time Central No from the registered mobile

In order to remove the bullets, I use the function, =Len() to find complete length then, =Right () to take number of characters backwards after deleting no of spaces of the bullet. (as per attachment). Then Paste special + value.

How can both the functions clubbed together ? Is it possible to do any macro / function in Column B5 itself.

Please suggest any solution.


VDS

 

[kchiba]

try

= RIGHT(B3, LEN(B3)-3)

 

[VDS]

@ Kanti,

Thanks. Can you suggest any macro without using the = RIGHT(B3, LEN(B3)-3).
Like the result can be on the same cell itself.


VDS

 

[kchiba]

try the following:

This assumes the data is in B3

Sub ReplaceData()
A = Range("B3").Value
A = Right(A, Len(A) - 3)
Range("B3") = A

End Sub

 

[VDS]

@ Kanti,

This is also good. This is applicable for a single row. One more query. If the number of rows are not specified (may be 10,15, 20, etc) how can we fix range ?


VDS

 

[kchiba]

in VBA you would put it into a loop, using either a For... Next, or Do While ... Loop.

You could also load the whole range into an array and then replace it all

Sub RepWithRange()
Dim ar As Variant
Set ar = Range("B5:B8")

For Each c In ar
c.Value = Right(c, Len(c) - 3)
Next c


End Sub

 

[Abhijeet R. Joshi]

To make it more simplier, you can use =MID(B3,4,270) and if you want to use this for a range you can just filldown the same (ctrl + D)

Macro code for a huge data:

Sub mid_fn()
Dim drc As Long
drc = Range("B" & Rows.Count).End(xlUp).Row
Range("C3").Formula = "= RIGHT(B3, LEN(B3)-3)" 'change to the starting row if not row#3
Range("C3:C" & drc).Select 'change to the starting row if not row#3
With Selection
.FillDown
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
Beep:
End Sub

 

[VDS]

@Abhijeet

The solution provided by you is excellent. From your function, =MID(B3,4,270), I understand B3, is the cell no, 4 is the no of digits to be removed (i.e bullets). But what is 270.

VDS

 

[Abhijeet R. Joshi]

if you consider the explanation of the MID function, it says =MID(text, start_num,num_chars), so here,
text is your value you want to bifurcate
start_num is the excluded part
Num_chars stands for the characters you want after the exclusion(in this case I have mentioned to consider 270 characters after initial 4 characters) you can change it to any number of your wish.

here is the macro code, if you wish to use the MID function:

Sub mid_fn()
Dim drc As Long
drc = Range("B" & Rows.Count).End(xlUp).Row
Range("C3").Formula = "=MID(B3,4,270)" 'change to the starting row if not row#3
Range("C3:C" & drc).Select 'change to the starting row if not row#3
With Selection
.FillDown
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
Beep:
End Sub

 

[VDS]

@Dear All,

Thanks too much for your valuable comments / suggestions. For example, whenever a query is being put up about the tale of an elephant, this forum is providing the entire history of elephant itself.

Keep up good work.

VDS

 

[shrivallabha]

You can also try and see if following works.
1. Select the range in which you want to replace this data. Invoke "Find And Replace" by pressing CTRL+H.
2. In Find type "*. " [without quotes asterisk+dot+space]
3. Keep Replace blank and replace all.

You should get what you want.

VBA macro if you record and clean up should look like this:

Sub ReplaceSrNo()
Range("B:B").Replace "*. ", vbNullString, xlPart
End Sub

 

[Abhijeet R. Joshi]

On similar lines, I think a text delimiter should work as well...
If its just a matter of removing the bullets/Sr.No.....
Dint come in my mind earlier...