Find/Replace 4 numbers in a sting of 17

[Lisa]

How can I use find and replace to change four consecutive zeros to 1135 with
out changing the rest of the number?
EX: 01000010100012711

Change to: 01113510100012711
Any suggestions would be greatly appreciated.

[Dave O]

This formula did it for me:
=IF(NOT(ISERROR(FIND("0000",A1,1))),MID(A1,1,FIND("0000",A1,1)-1)&"1135"&MID(A1,FIND("0000",A1,1)+4,LEN(A1)),A1)

This evaluates the entry in cell A1. If it finds a series of four
zeroes it parses the entry and replaces the four zeros with 1135. If
four consecutive zeroes are not found, it returns the value in A1.

Is it possible that 2 separate strings of four zeroes may appear? If
yes you'll need to run a similar formula on your *new* entries, as
well.

[Lisa]

It works like a champ, you are a genius! Thanks, I'd have never figured that
out.

"Dave O" wrote:

> This formula did it for me:
> =IF(NOT(ISERROR(FIND("0000",A1,1))),MID(A1,1,FIND("0000",A1,1)-1)&"1135"&MID(A1,FIND("0000",A1,1)+4,LEN(A1)),A1)
>
> This evaluates the entry in cell A1. If it finds a series of four
> zeroes it parses the entry and replaces the four zeros with 1135. If
> four consecutive zeroes are not found, it returns the value in A1.
>
> Is it possible that 2 separate strings of four zeroes may appear? If
> yes you'll need to run a similar formula on your *new* entries, as
> well.
>
>

[Lisa]

Thanks Dave, I am going to give it a try.

"Dave O" wrote:

> This formula did it for me:
> =IF(NOT(ISERROR(FIND("0000",A1,1))),MID(A1,1,FIND("0000",A1,1)-1)&"1135"&MID(A1,FIND("0000",A1,1)+4,LEN(A1)),A1)
>
> This evaluates the entry in cell A1. If it finds a series of four
> zeroes it parses the entry and replaces the four zeros with 1135. If
> four consecutive zeroes are not found, it returns the value in A1.
>
> Is it possible that 2 separate strings of four zeroes may appear? If
> yes you'll need to run a similar formula on your *new* entries, as
> well.
>
>

[JE McGimpsey]

One way, using a formula:

This assumes that you only want to change the first instance of 0000.

=SUBSTITUTE(A1,"0000","1135",1)

In article <[email protected]>,
"Lisa" <[email protected]> wrote:

> How can I use find and replace to change four consecutive zeros to 1135 with
> out changing the rest of the number?
> EX: 01000010100012711
>
> Change to: 01113510100012711
> Any suggestions would be greatly appreciated.

[AlfD]

Hi!

Try

=SUBSTITUTE(C1,"0000","1135")

where the original number (as text) is in C1.
I'm assuming "0000" will only happen once in any given cell.

Alf