slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Computer Science & Engineering 2111 PowerPoint Presentation
Download Presentation
Computer Science & Engineering 2111

Loading in 2 Seconds...

play fullscreen
1 / 19

Computer Science & Engineering 2111 - PowerPoint PPT Presentation


  • 111 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Computer Science & Engineering 2111' - orrick


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Computer Science & Engineering 2111

CSE 2111 Lecture

Text Functions

CSE 2111 Lecture-Text Functions

text functions
Text Functions

CSE 2111 Lecture-Text Functions

slide5

You receive a text file from a vendor every day.

You import the file into an Excel Workbook

CSE 2111 Lecture-Text Functions

slide6

The imported information looks as follows:

You need it to look as follows:

CSE 2111 Lecture-Text Functions

how trial and error
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

slide8
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

solution to step 1 concatenate and trim
Solution to Step 1: Concatenate and trim.

Problem: Rows of extraneous information

CSE 2111 Lecture-Text Functions

solution to step 2 identify rows with valid information
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

step 4 convert range to a table
Step 4: Convert range to a table

CSE 2111 Lecture-Text Functions

step 6 c opy and paste values into another worksheet
Step 6:Copy and paste values into another worksheet.

Old Worksheet

New Worksheet

CSE 2111 Lecture-Text Functions

step 7 convert text to columns
Step 7: Convert text to Columns

CSE 2111 Lecture-Text Functions

slide16

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

slide18

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

wow that was a huge amount of work
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