I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Awesome, thanks! I really liked all the shortcut key sequences. Do you have a list somewhere of the most useful excel shortcuts? Is there a way to remove spaces from column headers and leave an underscore (e.g. 'Academic year' to 'Academic_year')?
Yes, we have a complete list of Excel shortcuts in our Excel course (follow the links under the publication date under the video). Sure, you could use some type of find and replace function to swap in underscores for spaces, but if you only have to do it for a few column headings it's probably easier just to make the changes manually.
Mergers & Inquisitions / Breaking Into Wall Street Cool, I will check them out! There are many, many headings in the excel file I'm using. I'm editing data to use in SAS and I've found a way that SAS will accept spaces in the column headers: 'This is an example'n (just stick an 'n' on the end of your string). Thanks for the advice!
Thanks for the tutorial. Have you considered "INDIRECT" and "OFFSET" formulas to be volatile functions, meaning that these formulas will cause recalculation of the formula in the cell where it resides every time Excel recalculates and will significantly reduce speed as the workbook gets larger?
We do address this issue of volatile functions in our Excel course and use it to explain why INDEX/MATCH are better than OFFSET and the lookup functions (although in some cases, you must use INDIRECT).
Great video, I'm only wondering, why wouldn't you choose to format the second column of postcodes as texts so the zeros as they do not have any mathematical value and that would keep the zeros in front.
I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Thanks for sharing it. It's so useful for me and now i know the way to clean my database instead of cleaning it manually. Btw, could you have the video pertaining Macro? What is this and the purpose of Marco?
Thanks for watching! Macros are covered in our full Excel course, and we hope to create a few sample tutorials on them here soon. Macros help you automate common tasks, such as formatting spreadsheets a certain way or transforming data sets in a specific way.
No magic way to bring the preceding zero back without manually inserting it? To convert formula to text, I highlighted the formula areas and drag it back & forth & select "Copy to here as text". Short Cut key would be quick than mouse however. Thanks for the great video.
If postal code is always 6 numbers I guess you can say that if it were 5 (using the len function) numbers then the formula should be "0" + text version of number, if not then leave as is :D
Hi - I have an issue. I have something like Toronto on, Saint Catherine on Red Lake ab etc on different rows.. when i use space delimiter the Catherine and Lake also go into separate columns...can i tackle that? Thanks in advance!
Rajiv Chidambaram Hard to say without seeing your data. There is probably a way to do it, but you'll have to use a different delimiter or possibly use VBA if the data set is big and there's no easy way to add delimiters.
Use fixed width if there is some consistency in ur data.I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Any ideas when you have pulled in data and used the =proper function but the address now shows up like 123 Sw 12Th St when ideally you'd want that to be 123 SW 12TH ST?
You have to use the SUBSTITUTE function to search for those types of words and replace them. Proper won't work because it only capitalizes the first letter of each word.
Thanks - we do have transcripts and faster/slower play speeds for the lessons on our site if those would be helpful for you (UA-cam limits how much written description you can include).
You give great Information but you going through the functions way to fast. I appreciate the knowledge but have to keep going back to gather the shortcuts you are giving us.
Check this 1.I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Feel free to use a plugin to slow down or speed up video playback. We also allow slower playback at 0.75x speed in the full courses available on our site.
Perfect. Just what I was looking for. Nesting TRIM and PROPER functions.
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source.
Awesome video. Clear and concise. Easy to follow.
+Fan Xu Thanks for watching!
This video saved my life... Thank you!!
Thanks for watching!
I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Very informative - and delivered with some pace .Thank you.....
Great and slick video. Very informative. And yes, your data scrapes and extracts are not always predictably repeatable. Thanks again.
I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Okay.....consider yourself bear hugged!!! Thank you so much. I loved learning this efficient clean up! Muah!!!
Thank you very much, exactly what I need
Thanks for watching!
Very Useful session. Thanks
Thanks for watching!
Thank you so much for this video.
+PRAVEEN KUMAR Thanks for watching!
GREAT VIDEO, YOU SHOULD DO SOME MORE :)
It is a good video Thanks for the information.
Thanks for watching!
Awesome, thanks! I really liked all the shortcut key sequences. Do you have a list somewhere of the most useful excel shortcuts?
Is there a way to remove spaces from column headers and leave an underscore (e.g. 'Academic year' to 'Academic_year')?
Yes, we have a complete list of Excel shortcuts in our Excel course (follow the links under the publication date under the video).
Sure, you could use some type of find and replace function to swap in underscores for spaces, but if you only have to do it for a few column headings it's probably easier just to make the changes manually.
Mergers & Inquisitions / Breaking Into Wall Street Cool, I will check them out!
There are many, many headings in the excel file I'm using. I'm editing data to use in SAS and I've found a way that SAS will accept spaces in the column headers: 'This is an example'n (just stick an 'n' on the end of your string).
Thanks for the advice!
Thanks for the tutorial. Have you considered "INDIRECT" and "OFFSET" formulas to be volatile functions, meaning that these formulas will cause recalculation of the formula in the cell where it resides every time Excel recalculates and will significantly reduce speed as the workbook gets larger?
We do address this issue of volatile functions in our Excel course and use it to explain why INDEX/MATCH are better than OFFSET and the lookup functions (although in some cases, you must use INDIRECT).
Thanks that is a great shortcut ,Thanks again...
Thanks for watching!
Great video, I'm only wondering, why wouldn't you choose to format the second column of postcodes as texts so the zeros as they do not have any mathematical value and that would keep the zeros in front.
Yes, we could have done it that way as well. I don't recall why we used a different approach here.
I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Thank you
Thanks for watching!
Great video. Just wondering why you formatted the postal code as text rather than zip code from the special option. Thanks.
Yes, the zip code format would have been better. Did not think of it at the time + this is an older video.
AWSOME, GREAT JOB. thanks for the help. Can you please share the dataset, that we can download and practice on excel
This one is not available, but you can easily generate your own data to practice with: excelzoom.com/generating-random-data-for-your-excel-sheets/
Thank you so much
+mohamedmansor61 Thanks for watching!
Very helpful video. What pointer tool where you using to highlight the cell? Thank you
It's a part of Camtasia, the screen capture software we use. It is not a feature of Excel.
Very helpful video
Thanks for watching!
Thanks for videos. it helps me a lot however it would be awesome if u could show with macro.
useful course
this is great
Thanks for watching!
Thanks for sharing it. It's so useful for me and now i know the way to clean my database instead of cleaning it manually. Btw, could you have the video pertaining Macro? What is this and the purpose of Marco?
Thanks for watching! Macros are covered in our full Excel course, and we hope to create a few sample tutorials on them here soon. Macros help you automate common tasks, such as formatting spreadsheets a certain way or transforming data sets in a specific way.
Okay. Thanks 😄
I like the video its very useful
Thanks for watching!
No magic way to bring the preceding zero back without manually inserting it? To convert formula to text, I highlighted the formula areas and drag it back & forth & select "Copy to here as text". Short Cut key would be quick than mouse however. Thanks for the great video.
+Billy Lo Not an easy way, no. Sure, you can try that approach as well and use "Copy to here as text" to convert the formula to text.
If postal code is always 6 numbers I guess you can say that if it were 5 (using the len function) numbers then the formula should be "0" + text version of number, if not then leave as is :D
=text(cell,"00000")
for small dataset, we can change to text and add a "zero" for zip codes but for huge datasets.. how to do? is there any excel feature add zero?
You would probably have to count the number of digits with LEN and then add a trailing 0 in the beginning if there are only 4 digits rather than 5.
Hi - I have an issue. I have something like Toronto on, Saint Catherine on Red Lake ab etc on different rows.. when i use space delimiter the Catherine and Lake also go into separate columns...can i tackle that? Thanks in advance!
Rajiv Chidambaram Hard to say without seeing your data. There is probably a way to do it, but you'll have to use a different delimiter or possibly use VBA if the data set is big and there's no easy way to add delimiters.
Use fixed width if there is some consistency in ur data.I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Any ideas when you have pulled in data and used the =proper function but the address now shows up like 123 Sw 12Th St when ideally you'd want that to be 123 SW 12TH ST?
You have to use the SUBSTITUTE function to search for those types of words and replace them. Proper won't work because it only capitalizes the first letter of each word.
Use the UPPER() function to make it all upper case
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source.
Excel(lent) cleaning operation
very useful. but too fast
Thanks - we do have transcripts and faster/slower play speeds for the lessons on our site if those would be helpful for you (UA-cam limits how much written description you can include).
Mergers & Inquisitions / Breaking Into Wall Street
Thanks man i watched your video and learnt a lot, it solved my most complicated problem in excel.
Try pressing pause.
2:30 I thought wayne enterprises was in Gotham not Hampton
You're right, no idea how that slipped in there.
You give great Information but you going through the functions way to fast. I appreciate the knowledge but have to keep going back to gather the shortcuts you are giving us.
Check this 1.I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.
Nice, but it is too quick !
Feel free to use a plugin to slow down or speed up video playback. We also allow slower playback at 0.75x speed in the full courses available on our site.
Thanks for videos. it helps me a lot however it would be awesome if u could show with macro.
+Chhenghak Yang Thanks, we do feature tutorials on macros in our full Excel course.
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source.