Search and replace in the first part ONLY of cell

Originally Posted by MarvinP

Hi,

here is an example.. If you have "Bob8Sam8" in A1
then
=SUBSTITUTE(LEFT(A1,5),"8","S")&MID(A1,6,100)

Will only look at the first 5 characters of A1 and replace any/all "8" with the letter S.

It will give you an answer of "BobSSam8"


I hope this helps.

Hi,

Thank you for your help. However when I try to paste your formula I get the error in formula-message... Am I missing something?

edit.... duplicate

Originally Posted by xladept

Something like:

Please Login or Register  to view this content.

Hi, thank you for your response. When I try to run the code on my column A i get the error message "compilation error: matrix expected" (in swedish).

Also looking at the code - though I'm no programmer - doesn't it say "if cell is longer then six, then exit sub"? If that's the case that's quite not what I want to do. I still want to search and replace, but just in the first six spaces.

Originally Posted by MarvinP

Hi,
Find my sample workbook and see if you pasted the formula into the wrong cell??

It is a good chance that I did a function (Substitute()) that isn't in 2003 excel and started working with the 2007 version. That would explain why you get an error.

Sorry. Even Excel changes with more new functions in newer versions. If you had 2007 Excel or better, I bet my formula would work.

Thank you! I'm not sure what the problem was (maybe that the commands in my excel are in Swedish?), but now it worked like a charm. Recorded a macro using nested variations of this code and posted it pasted it to my main macro.

Originally Posted by xladept

To run it you need to have a cell selected. The < and > symbols relate to the positions on a number line, going to the left means less - to the right more

I rewrote the code like this (selecting column A):

Please Login or Register  to view this content.

But I still get this error message:

matrixexpected.jpg

Just realized it might be because I selected a column instead of a cell?

Anyway, the other solution worked, but so its not that important. Thanks anyhow!