Macro which searches for a character . When found delets what's be

A

andrei

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
 
M

Matthew Herbert

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
 
M

Mike H

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
 
E

Eduardo

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro which delets cell if the cell above has the same keyword 2
Need macro that analyses 2 columns . Delets row if same text is fo 3
Macro - searches for key word in a column and concatenates entire 6
Macro that splits content from cell if given character is found 4
Macro reads cells with numbers . If given number is found , delets 2
Macro - reads cells in a column .If keyword found moves cell conte 5
Macro that delets empty cells between cells with text 2
Macro that takes words from a sentence and puts them in diff cells 3

Top