1 / 7

=PROPER(CONCATENATE('Customer Data'!B4," ",'Customer Data'!A4))

1. Write an Excel text function in cell Company Data!A4, which may be copied down, to string together the first and last name with only the first letter of each begin capitalized. =PROPER(CONCATENATE('Customer Data'!B4," ",'Customer Data'!A4)).

perry-schwartz
Download Presentation

=PROPER(CONCATENATE('Customer Data'!B4," ",'Customer Data'!A4))

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. 1. Write an Excel text function in cell Company Data!A4, which may be copied down, to string together the first and last name with only the first letter of each begin capitalized . =PROPER(CONCATENATE('Customer Data'!B4," ",'Customer Data'!A4))

  2. 2. Write an Excel text function in cell Company Data!B4, which may be copied down to display the address with the letters, “St.” instead of “Street”. =SUBSTITUTE('Customer Data'!E4,"Street","St.")

  3. 3. Write an Excel text function in cell Company Data!C4, which may be copied down to display only the City. =LEFT('Customer Data'!F4,FIND(",",'Customer Data'!F4)-1)

  4. 4. Write an Excel text function in cell Company Data!D4, which may be copied down to display only the State. =MID('Customer Data'!F4,FIND(",",'Customer Data'!F4)+2,99) =RIGHT('Customer Data'!F4,LEN('Customer Data'!F4)- FIND(",",'Customer Data'!F4)-1) =MIDB('Customer Data'!F4,FIND(",",'Customer Data'!F4)+2,90)

  5. 5. Write an Excel text function in cell Company Data!E4, which may be copied down, to display the words, “INVALID ZIPCODE”, if the billing zip code is does not equal 5 characters.(Note: A blank zip code is valid.) =IF(OR(LEN('Customer Data'!J4)=5,LEN('Customer Data'!J4) =0), " “ ,"INVALID ZIPCODE")

  6. 6. Write an Excel text function in cell Company Data!F4, which may be copied down, to determine TRUE/FALSE if the company should use the customer’s billing address. =AND(EXACT('Customer Data'!E4,'Customer Data'!H4), EXACT('Customer Data'!F4,'Customer Data'!I4), EXACT('Customer Data'!G4, 'Customer Data'!J4))

  7. 7. Write an Excel text function in cell Company Data!G4, which may be copied down, to delete extraneous spaces in the Cleaning Type cells. =TRIM('Customer Data'!C4)

More Related