Macro to Delete characters from left and right

[gurtejhira]

I have to write macro for one column while keeping following scenarios in mind :
1. Remove first character from left side.
2. Remove all the characters from right side after "-" or "ER"

I will really appreciate if anyone can help me out.

Regards

 

[CalcSux78]

A macro may be fairly slow depending on how much data you have when you are wanting an entire column to equal something from another column.

Either way, try using the MID function. The start point is the second character, but you will have to find the end point. With VBA, use INSTRREV function(looks for string from right side of target string.) With excel, use a nested MID and FIND function such as:
=MID(B2,2,FIND("-",B2,1)-2) In this example, I am subtracting 2 because I don't want the hypen in my returned results.

 

[mumps]

Give this a try:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    On Error Resume Next
    For Each rng In Range("A1:A" & LastRow)
        rng = Left(Mid(rng, 2, 9999), WorksheetFunction.Find("-", rng) - 2)
        rng = Left(Mid(rng, 2, 9999), WorksheetFunction.Find("ER", rng) - 2)
    Next rng
    Application.ScreenUpdating = True
End Sub

This assumes the data is in column A. Change to suit your needs.

 

[gurtejhira]

I really appreciate both of you for prompt reply.

Mumps the code work perfectly great.

 

[mumps]

Glad it worked out.

 

[gurtejhira]

CalcSux

Is there a way to use if logic for "-" or "ER" in excel?

 

[gurtejhira]

Mumps now I am told to put one more logic if "ER" logic should only be used if "-" logic is not true.

Kindly guide.

 

[CalcSux78]

This worked for me... not sure if it's exactly what you are wanting. The formula in column C will first look for the hypen. If not found it searches for "ER" (case-sensitive). If neither is a match, it will return nothing.

Excel 2010

	A	B	C
1		FULL STRING	PARTIAL
2		1Test-Super	Test
3		2SecondERROR	Second
4		3SecondError

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IFERROR(MID(B2,2,IFERROR(FIND("-",B2,1),FIND("ER",B2,1))-2),"") C3 =IFERROR(MID(B3,2,IFERROR(FIND("-",B3,1),FIND("ER",B3,1))-2),"") C4 =IFERROR(MID(B4,2,IFERROR(FIND("-",B4,1),FIND("ER",B4,1))-2),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[gurtejhira]

Mumps

Thank you for your help. Please ignore my previous msg . Just one issue left whenever I try to use the vba code you provided its cutting first letter of the field even if its not "*" . Is it possible to fix that issue ?