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
Select all cells
Edit>Replace
HTH
--
AP
"AnimatorElf" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> 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
>
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
>
>
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.
Please post your sample data.
--
AP
"AnimatorElf" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> 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
>
"AnimatorElf" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
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" <[email protected]> wrote in message
news:[email protected]...
> "AnimatorElf" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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 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 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.
>
>
> --
> AnimatorElf
> ------------------------------------------------------------------------
> AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
> View this thread: http://www.excelforum.com/showthread...hreadid=543181
>
>
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
Ouch... I can't Sort them in any specific order... tells me I can't change an array.
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!
--
Thanks and kind regards
"AnimatorElf" wrote:
>
> Ouch... I can't Sort them in any specific order... tells me I can't
> change an array.
>
>
> --
> AnimatorElf
> ------------------------------------------------------------------------
> AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944
> View this thread: http://www.excelforum.com/showthread...hreadid=543181
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks