1 / 19

Computer Science & Engineering 2111

Computer Science & Engineering 2111. CSE 2111 Lecture Text Functions. Text Functions. You receive a text file from a vendor every day. You import the file into an Excel Workbook. The imported information looks as follows:. You need it to look as follows:. How!! Trial and Error.

orrick
Download Presentation

Computer Science & Engineering 2111

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Computer Science & Engineering 2111 CSE 2111 Lecture Text Functions CSE 2111 Lecture-Text Functions

  2. Text Functions CSE 2111 Lecture-Text Functions

  3. CSE 2111 Lecture-Text Functions

  4. CSE 2111 Lecture-Text Functions

  5. You receive a text file from a vendor every day. You import the file into an Excel Workbook CSE 2111 Lecture-Text Functions

  6. The imported information looks as follows: You need it to look as follows: CSE 2111 Lecture-Text Functions

  7. How!! Trial and Error • There is an Excel Feature called text to columns, but in order to use this you need all the information for each company in one cell and it has to be separated by commas. • Now is the time to “think outside of the box” • Write down the steps as you complete them so someone else can do this task if you are hit by a bus!  CSE 2111 Lecture-Text Functions

  8. Step 1: Concatenate the information for each company into column B1 for the first company, and so forth. Problem: Some of the cells begin with a space and the text to columns function will not work properly if there are extra spaces. CSE 2111 Lecture-Text Functions

  9. Solution to Step 1: Concatenate and trim. Problem: Rows of extraneous information CSE 2111 Lecture-Text Functions

  10. Solution to Step 2: Identify rows with valid information Observation: Cells with valid information end with the phone number Problem: What do I do with this information? CSE 2111 Lecture-Text Functions

  11. Step 3: Use the Find function to identify valid information CSE 2111 Lecture-Text Functions

  12. Step 4: Convert range to a table CSE 2111 Lecture-Text Functions

  13. Step 5: Filter to display only valid information CSE 2111 Lecture-Text Functions

  14. Step 6:Copy and paste values into another worksheet. Old Worksheet New Worksheet CSE 2111 Lecture-Text Functions

  15. Step 7: Convert text to Columns CSE 2111 Lecture-Text Functions

  16. Problem: State and Zip code are in the same cell. Why? They weren’t delimited the same as all the other information CSE 2111 Lecture-Text Functions

  17. Step 8: Separate State and Zip code into separate cells CSE 2111 Lecture-Text Functions

  18. Step 8: Insert a column between columns D and E, copy and paste the State to Column D and the Zip code to Column E (Make sure to paste values), then delete columns G and H.) Note: Make sure to remove green triangles from column E CSE 2111 Lecture-Text Functions

  19. WOW! That was a huge amount of work! It’s better than fixing 100,000 cells! But wait, we can do better than that. We can create a macro to automatically do these steps. (Shipping and handling not included) More information to follow…. CSE 2111 Lecture-Text Functions

More Related