|
Is there an option to do a "mass change"?
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 |
Is there an option to do a "mass change"?
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 |
Is there an option to do a "mass change"?
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 |
Is there an option to do a "mass change"?
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 |
Is there an option to do a "mass change"?
"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 |
Is there an option to do a "mass change"?
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 |
Is there an option to do a "mass change"?
"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 |
Is there an option to do a "mass change"?
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 |
Is there an option to do a "mass change"?
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 ------------------------------------------------------------------------ AnimatorElf's Profile: http://www.excelforum.com/member.php...o&userid=32944 View this thread: http://www.excelforum.com/showthread...hreadid=543181 |
Is there an option to do a "mass change"?
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 |
Is there an option to do a "mass change"?
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 |
| All times are GMT +1. The time now is 06:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com