Editing cell contents in VBA

[Mortadella]

I have run into a number of different situations editing specific cells in a macro but I am stuck on removing characters from a one cell line.


I get lists fairly often that would be terrifically useful to me if I could remove various characters from various cells.


Typically I get a line that would look like this:


100.70 The text length varies and is followed by either some letters or numbers 1000


If I need to get rid of the 100.70 I have found it's easiest to parse my stuff. I can insert characters at the beginning of a line but deleting characters seems to be beyond the fringe...


If I want to remove a bit of text from the middle of the line like 'either' I have had intermittent success if the text is in a fixed location in the cell but if it varies in length I get nowhere, so if you have any ideas on that I would be very grateful and ridding the trailing numbers is beyond me because I don't have the foggiest idea how to navigate from one end of a cells contents to the other.


I've looked all over the place but everything I've been able to find wants to add character - not take them out. I find that somewhat strange so I wonder if I'm not looking in the wrong place.


Can you help me out on this one?

Thanks!

 

[Hui]

Can you please paste 20 or so rows of typical data do we can see what your dealing with

 

[SirJB7]

Hi, Mortadella!


Try with this code:

-----

[pre]

Option Explicit

Sub GuttingStrings()
' constants
Const ksAddress = "A1"
Const ksRemove = "either"
' declarations
Dim nLeft As Single, sMiddle As String, vRight As Variant
Dim I As Integer, J As Integer, K As Integer, A As String, B As String
' start
A = ActiveSheet.Range(ksAddress).Value
' process
I = InStr(A, " ")
nLeft = Val(Left(A, I - 1))
J = InStr(StrReverse(A), " ")
vRight = Right(A, J - 1)
B = Mid(A, I + 1, Len(A) - I - J + 1)
K = InStr(B, ksRemove)
sMiddle = Left(B, K - 1) & Right(B, Len(B) - K - Len(ksRemove) + 1)
' end
Debug.Print nLeft, vRight, sMiddle
End Sub

[/pre]
-----


Where in nLeft you'll get the left single value, in vRight the rightmost letters or numbers, and in sMiddle the remaining string without the substring specified in ksRemove constant.


Just advise if any issue.


Regards!

 

[Smallman]

Mortadella


I agree with Hui here, can you post a sample of your data set or a workbook. I can glean from your message that you have data in a column sometimes numbers appear at the start, sometimes at the end and occasionally data appears mid text, all of which you want to remove. The following works on those set of assumptions. Obvo change the Replace term (“either”) to your word of choice. Assumes data is in Col A.

[pre]

Option Explicit
Sub GetRid()
Dim re As RegExp
Dim i As Integer
Set re = New RegExp

Columns(1).Replace "either", "" ' Get Rid of Either
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
re.Pattern = "d"
re.Global = True
Range("A" & i) = re.Replace(Range("A" & i), "")
Next i
End Sub

[/pre]

You will need to set up a connection in VB Project to the Microsoft Visual Basic Regular Expression n.n under Tools – References in the vb Editor.


Hope this helps.


Take care


Smallman