I need to make a macro remove the 7th character in a string of text if the
first 6 letters match the specified criteria. But I don't know the best way
to handle...

This is what I want to do:
If the first 6 characters of a2="Albany", then delete the next character
(which in this case is ",").

If the above isn't true...then continue on with the remainder of the macro.

And continue to the next row.

for each cell in selection
if instr(1,cell.Value,"Albany",VbTextCompare) = 1 then
cell.Value = left(cell.Value,6) & Mid(cell.value,8)
end if
Next

This would remove rich text formatting.

if you want it to be a case sensitive match change vbTextCompare to
vbBinaryCompare

You can speed it up by using the find method of the range object and only
going to cells that contain Albany. Look at Excel VBA help at the FindNext
sample code.


--
Regards,
Tom Ogilvy


"Josh O." wrote:

I need to make a macro remove the 7th character in a string of text if the
first 6 letters match the specified criteria. But I don't know the best way
to handle...

This is what I want to do:
If the first 6 characters of a2="Albany", then delete the next character
(which in this case is ",").

If the above isn't true...then continue on with the remainder of the macro.

And continue to the next row.

Thanks Tom. Work almost perfect. I just had to accomodate for the rare
occasion that "Albany" was followed by a space.

Appreciate the find next suggestion...I may implement that in near future.
For now this will work.

"Tom Ogilvy" wrote:

for each cell in selection
if instr(1,cell.Value,"Albany",VbTextCompare) = 1 then
cell.Value = left(cell.Value,6) & Mid(cell.value,8)
end if
Next

This would remove rich text formatting.

if you want it to be a case sensitive match change vbTextCompare to
vbBinaryCompare

You can speed it up by using the find method of the range object and only
going to cells that contain Albany. Look at Excel VBA help at the FindNext
sample code.


--
Regards,
Tom Ogilvy


"Josh O." wrote:

I need to make a macro remove the 7th character in a string of text if the
first 6 letters match the specified criteria. But I don't know the best way
to handle...

This is what I want to do:
If the first 6 characters of a2="Albany", then delete the next character
(which in this case is ",").

If the above isn't true...then continue on with the remainder of the macro.

And continue to the next row.