Is there an option to do a "mass change"?

[AnimatorElf]

Hello,
I thought I saw somewhere an option that would let me change a bunch o
cells at once.

My dilemma:
I have about 300 cells with a 'MMII #' in them (no quotes). The
represents a number from 1 to 500.

Is there a way to change all the 'MMII' in the cell to a '2:' and kee
the numbers? There is a space between the numbers and the MMII.

Thanks for your help.

Ala

 

[Ardus Petus]

Select all cells
Edit>Replace

HTH

 

[Guest]

Hi AnimatorElf!

You can also try the ff (Assuming your data is Sheet 1):

(1) Copy the range containing the MMII # data to new sheet (Sheet 2, but
same workbook) then paste
(2) Without moving the cursor in the new sheet, enter the ff formula...

=REPLACE(Sheet1Range,1,5,"2:")

where Sheet1Range refers to the original data; you should highlight this
Sheet1 range to be able to use the original data into the formula

(3) Press CTRL+SHIFT+ENTER

Hope this helps!

 

[AnimatorElf]

Thank you for the quick reply. But unfortunatly it doesn't work for al
of them.

I will select a bunch of the cells, do as you say and about the first
chance correctly, but then the rest turn into things like this:

0.126388889
0.129166667
0.129166667

I have no clue why the first few work and the rest dont. The cel
formats are all Text

 

[Ardus Petus]

Please post your sample data.

 

[David Biddulph]

"AnimatorElf" <[email protected]>
wrote in message

Thank you for the quick reply. But unfortunatly it doesn't work for all
of them.

I will select a bunch of the cells, do as you say and about the first 5
chance correctly, but then the rest turn into things like this:

0.126388889
0.126388889
0.129166667

I have no clue why the first few work and the rest dont. The cell
formats are all Text.

It sounds as if the content of the cells has been treated as times, rather
than text. Your two numbers look like 3:02 and 3:06, so I guess you had
2:62 and 2:66 , without any spaces after the "2:"? If you make sure you
keep a space after the "2:", then you may be more successful.

 

[AnimatorElf]

Here is my data:

Cells C2 thru C15 are:
MM 15
MM 20
MM 32
MM 62
MM 63
MM 65
MM 74
MM 90
MM 102
MM 102
MM 132
MM 149
MM 161
MM 163

I would like to make them:
1:15
1:20
1:32
1:62
1:63
1:65
1:74
1:90
1:102
1:102
1:132
1:149
1:161
1:163

But instead I get this when I do that Edit>Replace option:
1:09
1:28
1:37
0.084027778
0.134027778
0.136805556
0.142361111
0.143055556
0.14375
0.156944444
0.175
0.186805556
0.186805556
0.207638889

Thank you

 

[David Biddulph]

"AnimatorElf" <[email protected]>
wrote in message

It sounds as if the content of the cells has been treated as times, rather
than text. Your two numbers look like 3:02 and 3:06, so I guess you had
2:62 and 2:66 , without any spaces after the "2:"? If you make sure you
keep a space after the "2:", then you may be more successful.

.... but having tried my suggestion, the question is how one ensures that
there is a space, as the edit seems to ignore it. We'll let someone else
answer that! [I'm trying to fathom out what is different in your cells that
do work, as mine seems to go wrong on all cells.]

 

[Guest]

Hi again! As per example...

Assuming you want to the results on the column beside it (D215)...

Highlight D215 then enter the ff:

=REPLACE(C2:C15,1,3,"1:") then commit as CTRL+SHIFT+ENTER

Hope this helps!

 

[AnimatorElf]

That Replace formula worked wonders. Thank you

I just needed it in the same column they were in, not one next to it,
so I just made the "bad" column size '0' and hid them all.

Thanks a lot!

AnimatorElf

 

[AnimatorElf]

Ouch... I can't Sort them in any specific order... tells me I can't
change an array.

 

[Guest]

Hi again!

One workaround that we can do is to enter the array formula to the column
next to the data (as in my previous post). Once you get the desired results,
select the whole range of results from the array formula then select from the
menu Edit then Copy then Paste Special then select Value then OK. This
effectively "freezes" the results so you can now delete the first column
containing the original data (i.e. with the MM # format). Note also that the
array formula also is gone; now you can sort the data.

Additional note: make back ups of the file first before proceeding in case
the output is not what you've expected.

Hope this helps!