Need a function to replace text that meets condition located in a specific area of string

**bauman312** · 07-03-2012, 02:43 PM

Hey there,

I'm trying to figure out how to create a function that replaces the sequence " CR " with " CIR ". Here's the catch - there are a number of " CR " 's that I want to preserve; it is only the " CR " 's whose first character is located 6, 7, or 8 places from the right that I want to replace.

I've managed to replace all the " CR " 's that are on the end using the Right function:
=IF(RIGHT(H2,4)=" CR ",REPLACE(H2,SEARCH(RIGHT(H2,4),H2,4),5," CIR "),H2)

Now I would like to do this with the Mid function for those located 6,7,and 8 places from the right. However, they haven't worked as planned:
=IF(MID(H2,LEN(H2)-6,4)=" CR ",REPLACE(H2,SEARCH(MID(H2,LEN(H2)-6,4),5," CIR ")H2)

Can anybody help me on this?

Thanks!

**oeldere** · 07-03-2012, 03:04 PM

Please Login or Register  to view this content.

CTRL + H

find CR

replace CIR

replace all

**Søren Larsen** · 07-03-2012, 03:12 PM

Just for clarification: Might " CR " be present more than once in each string?

**bauman312** · 07-03-2012, 03:44 PM

Soren,

It's unlikely, but it could happen.
I've found a workaround by splitting the column by position, but would still be interested to know if anybody has any ideas. Oeldere, I don't think that's quite what I'm looking for. Thanks though.

**Søren Larsen** · 07-03-2012, 03:54 PM

If there is only 1 " CR " in each string, then you could probably use something like this:

Formula:

Please Login or Register  to view this content.

If there are mulitple " CR "s and you only want replace the ones on position 6, 7 and 8, and the length of the string varies, then you'll need a macro solution i think.

**oeldere** · 07-03-2012, 04:04 PM

Please Login or Register  to view this content.

You think, but have you tried?

**bauman312** · 07-03-2012, 04:11 PM

Soren,

Unfortunately that did not work. It changed all the " CR " 's, not just the ones positioned 6,7,and 8 places from the right. Additionally, some simply generated a "FALSE".

**bauman312** · 07-03-2012, 04:14 PM

Oeldere, that would replace all " CR "s. I want to only replace those that are positioned 6, 7, and 8 places from the right.

**oeldere** · 07-03-2012, 04:14 PM

I received an answer on my question

**Søren Larsen** · 07-03-2012, 04:18 PM

Originally Posted by bauman312

Soren,

Unfortunately that did not work. It changed all the " CR " 's, not just the ones positioned 6,7,and 8 places from the right. Additionally, some simply generated a "FALSE".

Yes, firstly I forgot something; change it to this:

Formula:

Please Login or Register  to view this content.

And with regards to it changing too many instances, then yes, that will happen if you have multiple " CR "s. Notice I edited my post #5. Could you give an example of strings containing only 1 " CR " and strings containing multiple?

**oeldere** · 07-03-2012, 04:25 PM

Please Login or Register  to view this content.

And even better, give also the wanted result.

**Søren Larsen** · 07-03-2012, 04:30 PM

Originally Posted by bauman312

Oeldere, that would replace all " CR "s. I want to only replace those that are positioned 6, 7, and 8 places from the right.

I only just realised that it was those positioned from the right. In that case the formula should be like so:

Formula:

Please Login or Register  to view this content.

**bauman312** · 07-03-2012, 06:40 PM

Perfect! That's exactly what I need. Out of curiosity, how come I can't add additional arguments to your formula? Say I wanted to apply the same logic to those " CR " s that are positioned 5 places from the right. When I try to do so, it says that there are too many arguments. Why is that so?

**Søren Larsen** · 07-03-2012, 06:43 PM

Then you've probably missed a "(", ")" or a "," or misplaced something. In the formula below I've added an extra argument and highlighted the changes in red:

Formula:

Please Login or Register  to view this content.

**bauman312** · 07-05-2012, 12:15 PM

Great, thanks Soren.

**Søren Larsen** · 07-05-2012, 12:49 PM

You're welcome; remember to mark your thread as solved...

Need a function to replace text that meets condition located in a specific area of string

LinkBack

Thread Tools

Rate This Thread

Display

Need a function to replace text that meets condition located in a specific area of string

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Re: Need a function to replace text that meets condition located in a specific area of str

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions