Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Excel Presentation (Xl 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    I was asked to give a MS-Excel presentation. This presentation has a title of how to deal with text using MS-Excel.

    I will cover:

    1) Text based worksheet formulas such as CHAR(), CLEAN(), TRIM(), CONCATINATE(), ... I mean all the formulas that are in the TEXT category.

    2) VBA Text manipulation functions, such as extracting the first letter of a string, extracting the nth letter of a string, finding same sequences in a string. Also from this lounge I can adapt some code to find dups, and things like that.

    I know that I will be asked to "align" two lists, in such a way that if a value is in both rows, these two values should be on the same row

    What else would you add?

    Where are neat sources of topics to add to this presentation? I have about 90 minutes, and I am sure that most of the time I will be dealing with explaining the components of the formula, and how to write them, but just in case, I can give them a hand out to take home with them.

    Thanks in advance.

    Cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Presentation (Xl 97 and >)

    Perhaps pointing them to the help file would get the most accomplished <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    The presentation you're going to do will open their eyes to some of the capabilities of excel, but searching the help has always proved useful <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Excel Presentation (Xl 97 and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Jeremy

    That is true. I did not add all what I will or want to do in my post. I did not want to bore the Loungers with such details, but I wanted to give an idea of what the presentation would feel like so that Loungers would have a focal point.

    I will be using the OLH to ease my work, but then I also need some things that are not listed in the OLH, some interesting case that a Lounger had to solve.

    I did search and scan the Lounge of ideas, but I still need more.

    Thanks again.

    Cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Presentation (Xl 97 and >)

    Wassim,
    Suggest consider the 'text to columns' tool as a sort of opposite of the concatenate function, and the ' trick for making what looks like a number into real rext. And mention, possibly, the ISNUMBER and ISTEXT logical tests.
    Best of luck.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Excel Presentation (Xl 97 and >)

    A query I often see and hear is one in which the user got last name, first name, middle initial (and various combos of those) from some other source, and it's all in ONE column -- maybe mainframe (what's that?) data or data from a Word (or other word processor) document -- and now needs to have the various names in separate columns.
    Chip Pearson has some nice code to address this text issue.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Presentation (Xl 97 and >)

    The famous *de-concatenate*....I've had to do that TOO many times. Perhaps MS Office should come up with a disclaimer : break data into small groups, it's easier to put together than break apart!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Excel Presentation (Xl 97 and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> John Ros

    <font color=blue><<< and the ' trick for making what looks like a number into real rext </font color=blue>

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>!!!

    I do not know what you refer to? Could you elaborate because I think this would be cool and useful <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>.

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Excel Presentation (Xl 97 and >)

    thankyou] KWeaver

    Yes!!! Good points, these should be included as well as how to deal with Part Numbers, phone numbers, and this kind of data.

    What else? <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Presentation (Xl 97 and >)

    <P ID="edit" class=small>(Edited by Jezza on 21-Aug-05 23:08. Search result did not save)</P>My favourite excel site at the moment is: www.exceltip.com

    I did a search on the site for text and Search for Text Should be a nice few to pad out 90 minutes....BTW Good Luck with the presentation
    Jerry

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Excel Presentation (Xl 97 and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Jerry

    I sure would like to bookmark this site as well. Seems like a nice one to check often

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Presentation (Xl 97 and >)

    Wassim

    I am not sure what level you are presenting too, but sometimes I find when I am teaching is to have some "Jezza Top Tips". In my day-to-day work I come across problems that always crop up. (I suppose I can this my previous job now as I move up in two weeks)

    Once I had a problem with was users who downloaded from csv and imported to excel. They found that the numbers were converted to text as they had not chaged the general setting in the import wizard. Add a 1 in a cell outside the data set copy, select range of data and paste special| multiply... It converts all to numerical values. This is just an example of course but "Wassim Top Tips" could be a nice way to " time fill" if you find you have some time left over , my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth
    Jerry

  12. #12
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Presentation (Xl 97 and >)

    Wassim,
    You've started a good thread, here, I can sort of feel a Star Post on the way?
    The ' trick I referred to is this:
    If you have a column of data containing 'numbers' which are only really identifiers - invoice numbers, telephone numbers, part numbers, etc - and not the sort of numbers which can be the meaningful subject of arithmetic, then unless the column is pre-formatted to the data type 'Text' (or a custom number format), any with leading zero characters will be 'improved by excel to suppress the leading zeroes.
    So 001234567 will become 1234567, etc.
    This is rarely what is wanted.
    The trick is to enter it as '001234567.
    The ' does not print. Nor does it display, except in the formula bar.
    And by default it enters as left-aligned, showing that it's a text string happening to contain number characters.
    If you really do want it to display and print, enter is as ''001234567
    Why anyone would ever want this, I can't see.
    Another workaround is to put one or more spaces in the string of 'numbers' - good for phone numbers as aids readability, but must be done consistently or sort orders may be screwed up.
    Sadly, putting a space at the front of the string doesn't work.
    So keep with the '.
    Wassim's Secret Trick, heh?

  13. #13
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Presentation (Xl 97 and >)

    More stuff to consider including?
    1. Don't write your autobiography in one cell of a spreadsheet as you're limited to 255 characters.
    Well, ordinary folk are. No doubt Hans, Steve, Jan Karel etc can hoodwink excel into ignoring this.
    2. Do mention text boxes as a way of getting 'unlimited' text into your workbook. Can also display the result of a formula, I believe. And much fancier colours, formatting ets than in a regular cell.
    3. Warn that Excel isn't wysiwyg. Well, it's not for me.
    4. Refer to formulas like
    ="On this figure, you could apply for a loan of up to about "&TEXT(VLOOKUP($BR$3,data!$A$1:$V$545,13,FALSE)*$B R$4,"

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Presentation (Xl 97 and >)

    <hr>in one cell of a spreadsheet as you're limited to 255 characters<hr>
    You can put up to 32,767 (2^15 -1) chars in a cell. The problem is that only about 1024 will display...

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Presentation (Xl 97 and >)

    Thanks for the correction, Steve, the point remains.
    Yet more for Wassim's tutorial...
    Be aware that the LEFT, RIGHT and MID text functions treat (probably sensibly) genuine digits of genuine numbers as text characters.
    A workaround is shown in the sample, attached.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •