Excel Tutorial - Cleaning Up Data with TRIM, PROPER, and Text to Columns

Поділитися
Вставка
  • Опубліковано 4 гру 2024

КОМЕНТАРІ • 78

  • @willkswill
    @willkswill 9 років тому +3

    Perfect. Just what I was looking for. Nesting TRIM and PROPER functions.

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source.

  • @MrXuFan
    @MrXuFan 9 років тому +1

    Awesome video. Clear and concise. Easy to follow.

  • @annacastaldi4290
    @annacastaldi4290 10 років тому +7

    This video saved my life... Thank you!!

    • @financialmodeling
      @financialmodeling  10 років тому +1

      Thanks for watching!

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      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.

  • @arthurscullion
    @arthurscullion 10 років тому +2

    Very informative - and delivered with some pace .Thank you.....

  • @infoseeker9151
    @infoseeker9151 7 років тому

    Great and slick video. Very informative. And yes, your data scrapes and extracts are not always predictably repeatable. Thanks again.

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      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.

  • @thememorytravelerReset
    @thememorytravelerReset 6 років тому +1

    Okay.....consider yourself bear hugged!!! Thank you so much. I loved learning this efficient clean up! Muah!!!

  • @PARTHPATEL-nv4tm
    @PARTHPATEL-nv4tm 4 роки тому +2

    Thank you very much, exactly what I need

  • @MdIslam-zj6yt
    @MdIslam-zj6yt 5 років тому +1

    Very Useful session. Thanks

  • @PRAVEENKUMAR-gi8qm
    @PRAVEENKUMAR-gi8qm 9 років тому +2

    Thank you so much for this video.

  • @nowdevoted1649
    @nowdevoted1649 7 років тому +3

    GREAT VIDEO, YOU SHOULD DO SOME MORE :)

  • @maheshkumar-ye3zm
    @maheshkumar-ye3zm 9 років тому +2

    It is a good video Thanks for the information.

  • @reneepardee1653
    @reneepardee1653 10 років тому +2

    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')?

    • @financialmodeling
      @financialmodeling  10 років тому

      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.

    • @reneepardee1653
      @reneepardee1653 10 років тому

      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!

  • @victorhuang36
    @victorhuang36 6 років тому +1

    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?

    • @financialmodeling
      @financialmodeling  6 років тому +1

      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).

  • @rebeccashetter2309
    @rebeccashetter2309 9 років тому

    Thanks that is a great shortcut ,Thanks again...

  • @HWacko93
    @HWacko93 6 років тому +1

    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.

    • @financialmodeling
      @financialmodeling  6 років тому

      Yes, we could have done it that way as well. I don't recall why we used a different approach here.

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      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.

  • @sanchita4189
    @sanchita4189 5 років тому +1

    Thank you

  • @JM-zm6fl
    @JM-zm6fl 5 років тому

    Great video. Just wondering why you formatted the postal code as text rather than zip code from the special option. Thanks.

    • @financialmodeling
      @financialmodeling  5 років тому

      Yes, the zip code format would have been better. Did not think of it at the time + this is an older video.

  • @Explorer_00-p5n
    @Explorer_00-p5n 6 років тому

    AWSOME, GREAT JOB. thanks for the help. Can you please share the dataset, that we can download and practice on excel

    • @financialmodeling
      @financialmodeling  6 років тому

      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/

  • @mohamedmansor61
    @mohamedmansor61 8 років тому +3

    Thank you so much

  • @armandofranco6998
    @armandofranco6998 5 років тому

    Very helpful video. What pointer tool where you using to highlight the cell? Thank you

    • @financialmodeling
      @financialmodeling  5 років тому

      It's a part of Camtasia, the screen capture software we use. It is not a feature of Excel.

  • @kenlee6938
    @kenlee6938 9 років тому

    Very helpful video

  • @Ychhenghak
    @Ychhenghak 8 років тому

    Thanks for videos. it helps me a lot however it would be awesome if u could show with macro.

  • @ihabziyad8611
    @ihabziyad8611 6 років тому +1

    useful course

  • @gihanperera8741
    @gihanperera8741 9 років тому +2

    this is great

  • @eleanfam1369
    @eleanfam1369 10 років тому

    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?

    • @financialmodeling
      @financialmodeling  10 років тому

      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.

    • @eleanfam1369
      @eleanfam1369 10 років тому

      Okay. Thanks 😄

  • @godlizenmoshiituwe5028
    @godlizenmoshiituwe5028 9 років тому

    I like the video its very useful

  • @lobilly
    @lobilly 9 років тому

    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.

    • @financialmodeling
      @financialmodeling  9 років тому

      +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.

    • @patrickwong2867
      @patrickwong2867 8 років тому

      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

    • @SteveKubiak
      @SteveKubiak 5 років тому

      =text(cell,"00000")

  • @Explorer_00-p5n
    @Explorer_00-p5n 6 років тому

    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?

    • @financialmodeling
      @financialmodeling  6 років тому

      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.

  • @rajiv2612
    @rajiv2612 9 років тому

    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!

    • @financialmodeling
      @financialmodeling  9 років тому

      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.

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      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.

  • @davidsanders2626
    @davidsanders2626 5 років тому

    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?

    • @financialmodeling
      @financialmodeling  5 років тому +1

      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.

    • @fuddam
      @fuddam 5 років тому

      Use the UPPER() function to make it all upper case

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source.

  • @mahamohan1
    @mahamohan1 6 років тому +1

    Excel(lent) cleaning operation

  • @Dishani1230
    @Dishani1230 10 років тому +5

    very useful. but too fast

    • @financialmodeling
      @financialmodeling  10 років тому

      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).

    • @bscacademy
      @bscacademy 9 років тому

      Mergers & Inquisitions / Breaking Into Wall Street
      Thanks man i watched your video and learnt a lot, it solved my most complicated problem in excel.

    • @arkady714
      @arkady714 6 років тому

      Try pressing pause.

  • @irvkoi
    @irvkoi 4 роки тому

    2:30 I thought wayne enterprises was in Gotham not Hampton

  • @CoryHendersonamorsupply
    @CoryHendersonamorsupply 6 років тому +1

    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.

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      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.

  • @mikno78
    @mikno78 9 років тому

    Nice, but it is too quick !

    • @financialmodeling
      @financialmodeling  9 років тому

      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.

  • @Ychhenghak
    @Ychhenghak 8 років тому

    Thanks for videos. it helps me a lot however it would be awesome if u could show with macro.

    • @financialmodeling
      @financialmodeling  8 років тому

      +Chhenghak Yang Thanks, we do feature tutorials on macros in our full Excel course.

    • @SIMRANKAUR-eg7nc
      @SIMRANKAUR-eg7nc 4 роки тому

      Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source.