View Full Version : Microsoft Excel Gurus..
optiklenz13
03-18-2010, 11:13 AM
So I'm working on this write - off file and I have like 16,000 values to update. Is there any macro that can be created to put a preceding 0 on a cell instead of going through all of them manually?
See attached, if you see Row 193 to 196, I pretty much need Row 198 and so on to show that value.
Anyone?
:)
whodaman
03-18-2010, 11:30 AM
if you highlight all the numbers you have left, right click and go to format cells...under 'category' in the 'numbers' tab....go to custom.
on the right side under 'type'...put in the value 0######## (the number of #'s should be more than your largest number).
hope that helps.
optiklenz13
03-18-2010, 11:38 AM
ahhh!!! you're the man!!! thanks man!! worked like a charm!! didn't even think about that.
/thread
eqlso
03-18-2010, 11:40 AM
or you can go to c1 and type in
="0" & b1
then copy c1 and paste it over c2 to whatever it ends at
eqlso
03-18-2010, 11:41 AM
man too late lol
optiklenz13
03-18-2010, 11:45 AM
hmm.. now I have issues with that 0####### solution..
I have a macro I created to get the first two digits transferred over to column A and when I run that Macro, it doesn't pick up the 0. :( oh well, I guess I have to modify the macro itself.
Noisy Crow
03-18-2010, 11:52 AM
hmm.. now I have issues with that 0####### solution..
I have a macro I created to get the first two digits transferred over to column A and when I run that Macro, it doesn't pick up the 0. :( oh well, I guess I have to modify the macro itself.
Create a temporary column. Format the entire thing as text. Copy the column with your formatted numbers and "paste special" as text.. Copy the contents of the new column and paste over the original. Delete the temp column.
optiklenz13
03-18-2010, 12:03 PM
I don't see any option for text on paste special. :(
http://i228.photobucket.com/albums/ee173/optiklenz13/paste.jpg
kLuMzi
03-18-2010, 12:08 PM
I don't see any option for text on paste special. :(
http://i228.photobucket.com/albums/ee173/optiklenz13/paste.jpg
try "Values"
Noisy Crow
03-18-2010, 12:13 PM
I don't think what I suggested will work (I should not post when in a meeting at work!)
This does:
create the temp column
put in the "0" & B1 or whatever in the first cell of the temp column and copy it down.
Select & copy the temp column and then use paste-special , values, to paste over the original column
whodaman
03-18-2010, 12:23 PM
my two cents again.....in the column beside the first number type...=left(D1,2)...if your first value is in cell D1 for example. this will truncate the number to the first two digits, but it wont keep the zero in front. to get the zero and the first two numbers...in the next cell over (say F1), enter...=TEXT(E1,"0##"), this will add the zero to the value in E1.
there may be a quicker way, but off the top of my head this is what i came up with!!!
good luck!
optiklenz13
03-18-2010, 12:43 PM
I don't think what I suggested will work (I should not post when in a meeting at work!)
This does:
create the temp column
put in the "0" & B1 or whatever in the first cell of the temp column and copy it down.
Select & copy the temp column and then use paste-special , values, to paste over the original column
is that supposed to be a formula? i think i'm confusing myself even more here now. LOL.
optiklenz13
03-18-2010, 12:45 PM
nevermind!! Noisy Crow, you are a genius sir.. it worked..
thanks again!!!
optiklenz13
03-18-2010, 12:49 PM
http://i228.photobucket.com/albums/ee173/optiklenz13/spreadsheet-1.jpg
:) holla!!!
kLuMzi
03-18-2010, 02:19 PM
^^
Good job! :)
guess it's my turn to ask LOL been searching the internet but nothing worked.
http://i5.photobucket.com/albums/y186/jeypz26/excel.jpg
Ok, what I want to do is to sort those dates in ascending order.
How do i convert those texts into dates? it's not as simple as changing the format of the cells. i think formulas are invovled? before i can sort by date
whodaman
03-18-2010, 02:52 PM
alright lets see about sorting those dates! In the cell B1 type...=right(A1,2)...then drag that cell down until the last date (so you get =right(A2,2), =right(A3,2) etc.) then highlight both rows and click on the asending order button. they should all be sorted from earliest to most recent. then you can just delete the data from the 'B' column.
hope that made sense
kLuMzi
03-18-2010, 03:07 PM
^^
thanks! ok we have progress here
http://i5.photobucket.com/albums/y186/jeypz26/excel1.jpg
the year is now good, but cells with same year were still sorted by text (08: Apr, Dec, Oct) =\
Noisy Crow
03-18-2010, 04:21 PM
Use the datevalue function
eg. in your new column, put: =DateValue(A1)
iconicrocket
11-04-2012, 08:06 PM
Need to know how to delete duplicate entries in the same column
http://i130.photobucket.com/albums/p258/iconicrocket/dupe.png
How to compare 2 columns and highlight which entry is missing in the other column
http://i130.photobucket.com/albums/p258/iconicrocket/compare.png
eqlso
11-04-2012, 08:23 PM
The first one, there's a remove duplicates button in the data tab.
Second one I'm not sure, probably can do a vlookup on it.
The first one, there's a remove duplicates button in the data tab.
Second one I'm not sure, probably can do a vlookup on it.
Or use brute force (ie a loop with arrays) for the second one
Powered by vBulletin® Copyright © 2025 vBulletin Solutions, Inc. All rights reserved.