How do I change replace text in a cell for different text?

[ronedwards]

have a couple of thousand lines of information that basically is this:

Xxxxxxx_Xxxxxxx Xxxxxxx Xxxxxx_YYY_Xxx.Zzz

Where X is a letter, Y a number, Zzz the file extension.

The information in the cell is not a formula.

I need to be able to

a/ change the file extension name (all going from one the same to a
different one the same)

b/ remove the YYY_Xxx before the file extension, but the other info
stays the same.

I'm sure there is a convaluted formula, or maybe an easy one?!?!

TIA!

 

[Bernie Deitrick]

Are all the file names the same length? If so, it could be a easy as:

=LEFT(A1,32) & ".ext"

where ext should be replace with the new extension.

If they are different lengths, then perhaps:

=LEFT(A1,LEN(A1)- 11) & ".ext"

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Are all the file names the same length? If so, it could be a easy as:

=LEFT(A1,32) & ".ext"

where ext should be replace with the new extension.

If they are different lengths, then perhaps:

=LEFT(A1,LEN(A1)- 11) & ".ext"

HTH,
Bernie
MS Excel MVP

 

[ronedwards]

-If they are different lengths, then perhaps:

=LEFT(A1,LEN(A1)- 11) & ".ext"-

Thanks - yes they are all different lengths, with "_YYY_Xxx.Zzz" bein
the only constant. When using your formula, it changes the .ext, bu
doesn't leave the filenames at the correct lengths....

 

[ronedwards]

-If they are different lengths, then perhaps:

=LEFT(A1,LEN(A1)- 11) & ".ext"-

Thanks - yes they are all different lengths, with "_YYY_Xxx.Zzz" bein
the only constant. When using your formula, it changes the .ext, bu
doesn't leave the filenames at the correct lengths....

 

[ronedwards]

SORRY - my bad - it does work, I was using the wrong column.....
THANKS!!

 

[ronedwards]

SORRY - my bad - it does work, I was using the wrong column.....
THANKS!!

 

[Bernie Deitrick]

If I have

Xxxxxxx_Xxxxxxx Xxxxxxx Xxxxxx_YYY_Xxx.Zzz

in cell A1, this formula in B1:

=LEFT(A1,LEN(A1)- 11) & ".QQQ"

returns

Xxxxxxx_Xxxxxxx Xxxxxxx Xxxxxx_.QQQ

Since it is now "_YYY_Xxx.Zzz" that is constant (and not "YYY_Xxx.Zzz" as
you first said) the formula

=LEFT(A1,LEN(A1)- 12) & ".QQQ"

will replace the "_YYY_Xxx.Zzz" with ".QQQ" and return

Xxxxxxx_Xxxxxxx Xxxxxxx Xxxxxx.QQQ

So I don't see what your problem with the formula is...

Bernie

 

[Bernie Deitrick]

If I have

Xxxxxxx_Xxxxxxx Xxxxxxx Xxxxxx_YYY_Xxx.Zzz

in cell A1, this formula in B1:

=LEFT(A1,LEN(A1)- 11) & ".QQQ"

returns

Xxxxxxx_Xxxxxxx Xxxxxxx Xxxxxx_.QQQ

Since it is now "_YYY_Xxx.Zzz" that is constant (and not "YYY_Xxx.Zzz" as
you first said) the formula

=LEFT(A1,LEN(A1)- 12) & ".QQQ"

will replace the "_YYY_Xxx.Zzz" with ".QQQ" and return

Xxxxxxx_Xxxxxxx Xxxxxxx Xxxxxx.QQQ

So I don't see what your problem with the formula is...

Bernie