# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Delete everything after a certain character

## dadel

Hi,
I've got some cells that contain names and numbers within parentheses. (the cells located in the A column, starting on cell 2)




> john(5)
> jack(90)
> joe(120)



I only want to keep the names, so I want to delete the parentheses and everything in them.

Since the parentheses is always last in a cell, my vba function should find the first parenthesis, and then delete it and everything that comes afterwards.

I've figured out that i can use 'InStr' to check if the cell contains a parenthesis.
But I need some help with the deleting part.




> Public Sub DelEverythingAfterParenthesis()
> Dim rngCell As Range
> Set ws = Workbooks("a.xls").Sheets("sheet1")
> For Each rngCell In ws.Range(Cells(2, "A"), ws.Cells(Rows.Count, "A").End(xlUp)).Cells
>     If (InStr(rngCell, "(") > 0) Then
> 
> //Some code to delete the parentheses and everything after it.
> 
>     End If
> ...

----------


## vikas.bhandari

You dont have to write a macro for this.

Write this formula in C2 and drag it down to the number of rows you want to apply to : 

=LEFT(B2,FIND("(",B2,1) - 1)

HTH.

Thanks,
Vikas

----------


## dadel

Thank you! 
that did the trick. (changed b2 to a2 though)

But a wierd problem - while that worked in a test document, I can't get the same forumula working in the document i'm working on!
The cell just displays the forumla, it doesn't calculate the result, even if I press enter.

The cells in both documents look the same, have the same format (text), and the same values.
could there be any problems with the settings or something like that?

----------


## DonkeyOte

Highlight the column containing the formula then

ALT + D -> E -> click Finish

If you format cells to Text prior to entering the formula then the formula will be viewed as a Text string and not a formula, the above corrects that issue.

----------


## mthem2003

I have a similar question:

how can i delete Everything before a certain character ( for example @ ), no matter how much there is before @

----------


## Leith Ross

Hello mthem2003,

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------

