Hello,
I thought I saw somewhere an option that would let me change a bunch of
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 keep
the numbers? There is a space between the numbers and the MMII.

Thanks for your help.

Alan


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=543181

Select all cells
EditReplace

HTH
--
AP

"AnimatorElf" a
écrit dans le message de news:
...

Hello,
I thought I saw somewhere an option that would let me change a bunch of
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 keep
the numbers? There is a space between the numbers and the MMII.

Thanks for your help.

Alan


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile:
http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=543181

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.129166667
0.129166667

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


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=543181

Please post your sample data.

--
AP

"AnimatorElf" a
écrit dans le message de news:
...

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.129166667
0.129166667

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


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile:
http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=543181

Here is my data:

Cells C2 thru C15 a
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 EditReplace 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.


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=543181

Hi again! As per example...

Assuming you want to the results on the column beside it (D2:D15)...

Highlight D2:D15 then enter the ff:

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

Hope this helps!
--
Thanks and kind regards


"AnimatorElf" wrote:


Here is my data:

Cells C2 thru C15 a
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 EditReplace 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.


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=543181

"AnimatorElf"
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.
--
David Biddulph

"David Biddulph" wrote in message
...
"AnimatorElf"
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.

.... 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.]
--
David Biddulph

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!
--
Thanks and kind regards


"AnimatorElf" wrote:


Hello,
I thought I saw somewhere an option that would let me change a bunch of
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 keep
the numbers? There is a space between the numbers and the MMII.

Thanks for your help.

Alan


--
AnimatorElf
------------------------------------------------------------------------
AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
View this thread: http://www.excelforum.com/showthread...hreadid=543181