I need a macro which searches cells . When it finds a given character ( in my
case the character needed is : ) , it delets all characters including the
given character . Preferably it puts the result in same column

Example :

A1 mother:son
A2 father

The result should be

A1 son
A2 father

It's ok if it puts the result in column B , no problem with that , but
better in A column

Andrei,

Take a look at the native text functions in Excel. Open the "Insert
Function" dialog box, select the Text category, and read about the text
functions. Pay attention to FIND/SEARCH, LEN, RIGHT, and LEFT. You'll be
able to FIND ":" and return the characters to the RIGHT of ":".

Best,

Matthew Herbert

"andrei" wrote:

I need a macro which searches cells . When it finds a given character ( in my
case the character needed is : ) , it delets all characters including the
given character . Preferably it puts the result in same column

Example :

A1 mother:son
A2 father

The result should be

A1 son
A2 father

It's ok if it puts the result in column B , no problem with that , but
better in A column

Hi,

Try this

Sub stance()
Dim Lastrow As Long
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
c.Value = Mid(c.Value, InStr(c.Value, ":") + 1)
Next
End Sub


Mike

"andrei" wrote:

I need a macro which searches cells . When it finds a given character ( in my
case the character needed is : ) , it delets all characters including the
given character . Preferably it puts the result in same column

Example :

A1 mother:son
A2 father

The result should be

A1 son
A2 father

It's ok if it puts the result in column B , no problem with that , but
better in A column

Hi,
in column B enter

=MID(A1,SEARCH(":",A1)+1,256)

then you can copy column B as value on top of column A and delete column B

"andrei" wrote:

I need a macro which searches cells . When it finds a given character ( in my
case the character needed is : ) , it delets all characters including the
given character . Preferably it puts the result in same column

Example :

A1 mother:son
A2 father

The result should be

A1 son
A2 father

It's ok if it puts the result in column B , no problem with that , but
better in A column

Thanks guys . The macro works beautiful . So does the function given by Eduard