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

R

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!
 
B

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
 
R

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....
 
B

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
 
Top