Formula Required..

[Safequip]

Small - XX Large

Small -X Large

Small -Large

Small -XX Large

Small-Medium/Large

Small - XX Large

Small -XX Large

Small -X Large


need to change to S-XXL and so forth, is this possible?

 

[vasim]

Find and replace all Small to S & Large to L


if want to achieve through Formula use =Substitute...

 

[jason]

perhaps you can provide more details on what your looking for.... are you wanting to keep the info you have above in one column and have the abbreveated version in a separate col?


maybe show how want all of the to 'look' S-XXL, S-XL, S-L, S-M/L?? like this?


a formula with varis nested text function may be able to work but the enteries (as you have them) are not consistant enough to make it 'easy'. (spaces after the first -)

 

[Safequip]

Hi Jason yeah l would like it to look like S-XXL etc... I can keep the info and hide the Colum or just replace the existing. Another query I have (sorry) do you know how I ensure my first two rows are printed on each sheet?


Thanks

 

[jason]

Page Setup: Print Titles


i'll see if i can figure something out formula wise?

 

[Safequip]

cheers x

 

[Absolution]

Long winded but try:


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4," ",""),"-"," - "),"Small","S"),"X Large","XL"),"Large","L"),"Medium","M")


where B4 is the cell to be changed.

 

[SirJB7]

Hi, Safequip!


A little twist to Absolution's formula, to remove spaces as you required:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(B4;" ";"");"Small";"S");"Medium";"M");"Large";"L") -----> in english: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4," ",""),"Small","S"),"Medium","M"),"Large","L")


And about printing the first 2 rows on each sheet do this:

File, Print, Page setup, Sheet (last tab, 4th), and enter 1:2 in Repeat Rows on Top.

You'd either repeat columns at left with same procedure.


Regards!