Jump to content


Start a free course now!

Most Liked Content


#25 Do you have similar courses for Word, PowerPoint and other applications?

Posted by TheSmartMethod on 02 September 2013 - 03:27 PM

The only Smart Method courses that have been published as books/videos so far are:

 

• Learn Access 2003 VBA With The Smart Method ISBN-13: 978-0955459900

• Learn Excel 2007 Essential Skills with the Smart Method ISBN-13: 978-0955459924

• Learn Excel 2007 Essential Skills with the Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2007 Expert Skills with The Smart MethodISBN-13: 978-0955459931

• Learn Excel 2007 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459979

• Learn Excel 2010 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Expert Skills with The Smart Method ISBN-13: 978-0-9554599-8-6

• Learn Excel 2010 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Essential Skills with The Smart Method ISBN:  978-1-909253-06-3

• Learn Excel 2013 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Expert Skills with The Smart Method ISBN:  978-1-909253-07-0

• Learn Excel 2016 Essential Skills with The Smart Method ISBN: 978-1-909253-08-7

• Learn Excel 2016 Expert Skills with The Smart Method ISBN: 978-1-909253-09-4

Learn Excel 2016 for Mac Essential Skills with The Smart Method ISBN: 978-1-909253-11-7

Learn Excel 2016 for Mac Expert Skills with The Smart Method ISBN: 978-1-909253-12-4

Excel Construction Kit #1: Calendar and Year Planner ISBN: 978-1-909253-10-0

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459962

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method ISBN-13:978-0-9554599-9-3

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Essential Skills with The Smart Method ISBN-13: 978-1-909253-04-9

• Learn ASP.NET 4.5, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method ISBN-13: 978-1-909253-05-6

• Learn ASP.NET 4.5, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

 

We're currently working on an (almost completed) VBA book  The VBA book will teach the Visual Basic for Applications (VBA) programming language which is generic to Office 2013 and not specific to Excel or any other application. 

 

Eventually Mike Smart will also write up his Outlook, Access, Word, Project, Power Point… and other courses as books and videos but it will take many years to complete them all. The Smart Method are currently exploring ways of increasing productivity by employing proof-readers,  testers and video editors to free more of Mike's time for course design and recording.  




#26 Do you have an Excel VBA (Visual Basic for Applications) course available?

Posted by TheSmartMethod on 02 September 2013 - 03:31 PM

VBA is not an Excel skill but a programming language that can be used (amongst other things) to automate Excel and other applications. The Smart Method have run VBA classroom courses (in the context of both the Access and Excel object models) for over ten years.

 

After many years of classroom courses for some of the world’s largest companies it has become apparent that very, very few business users actually need Visual Basic programming skills. In most business scenarios Excel is more than capable of providing every corporate need by correct use of its in-built functionality.

 

We've often been asked to provide a VBA classroom training course but then changed the course to Expert Skills on the day. This has happened because when delegates describe the business problems that they think VBA will help them solve, the solution is easy to implement by correctly understanding Excel’s advanced functionality.

 

If you have a specific business problem that you think that you need VBA to solve why not post it on our support forum.  One of our Excel Experts will, in almost all cases, be able to provide a solution that does not involve bespoke programming.



#927 Do you offer any training for Word

Posted by Prasanna on 17 April 2014 - 06:16 AM

Hi

Is there any training for Microsoft Word. This training is best in the world for Excel. If you have training for Microsoft Word such WordCentral.Com, i am very happy to get this one.

 

 

Regards

Prasnana




#1742 Preparing for the Microsoft MOS Expert Exam 77-427 and 77-428 using The Smart...

Posted by Mike on 04 July 2015 - 09:56 AM

Dear Ishaq

 

The new MOS exam format (for Office 2013) doesn't seek to test any individual Excel skills but, instead, poses a real-world business problem that the student must solve.  This is a very welcome change from earlier exams for which it was impossible to "guess" which skills Microsoft actually were going to test (due to very loosely defined objectives).

 

This is from the Microsoft site:

 

"The new exam format for MOS 2013 presents a short project that the candidate must complete, using the specifications provided. This creates a real-world testing experience for candidates". 
 
Microsoft advise that Excel Skills are tested in the way that they are taught in our courses -  in the context of solving a real business problem.  Our teaching method -  with every skill taught using an example workbook from the real world of business and commerce - should prove ideal for preparation for your exam.  You'll also learn many more extremely useful skills that go well beyond those included in the MOS "Expert" certification. 
 
If you complete our Expert course I know that you will be able to address any real-world business problem for which Excel is an appropriate tool.  If you look at some of the real-world business problems posed on this forum by professional Excel users,  you'll see that many are extremely complex but that all have been solved using only the skills taught in our course. (The solutions are always cross-referenced back to video and e-book lessons that teach the skills used in the solution) .
 
I'd suggest that you proceed as follows:
 
1/ Work through the 2013 Essential Skills and Expert Skills courses until you can complete all of the exercises at the end of each session without referring back to the book or video lesson.
 
2/ Study the problems posed on this forum until you thoroughly understand the solutions we have provided.
 
If you do take (and hopefully pass) the test it would be very interesting if you could post details of the project you were asked to complete and we'll provide a worked example of the solution by referring to the relevant lessons in the Essentials and Expert course.
 
I should also mention that  you will receive our own certification of course completion at the end of our course that you can print out and e-mail directly from your control panel (available when you log-in).    
 
Best Regards

 

Mike Smart




#86 Forum Rules - Please read before posting

Posted by TheSmartMethod on 03 September 2013 - 01:03 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.



#85 Forum Rules - Please read before posting

Posted by TheSmartMethod on 03 September 2013 - 01:03 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.



#793 Length of Courses

Posted by Mike on 14 March 2014 - 12:28 AM

Hi

 

How long it takes you to compete either course is (of course) extremely variable. A lot depends upon whether you are completely new to Excel or whether there is a degree of past exposure.

 

Most users of Excel do not need Expert Skills.  To work in the average office job you will usually be hugely in advance of the average other users if you "only" have the skills described in the "Essential Skills" course. 

 

The books are based upon Smart Method classroom courses.  With well motivated and above average intelligence adult course attendees, with zero previous Excel experience, we have trained thousands of Excel users (without exception) to "Essential Skills" standard in one eight hour intensive day.  Unfortunately this is in a classroom environment (and hugely expensive compared to the cost of the book or video course).

 

Just to listen to each Essential Skills video lesson will take around eight hours.  In a (non classroom) environment, using only books or videos as the learning material, I would guess that three days (of intensive attention) would be the maximum needed by most adult learners to master every skill presented.

 

Expert Skills is a little more advanced. 

 

To truly achieve Expert status I ask my corporate clients to allocate two days of intensive classroom training (for users with existing good Excel skills).  In effect most go for one day and it is possible to "cherry pick" different skills to cover those most of relevance to the class in this time frame.  For self study it would be good to double this estimate to 2-4 days depending if you need to know "everything" or a subset of the skills presented.  But once again, you need to ask yourself if you really need Expert Skills (few users really do).

 

Best Regards

 

 

Mike Smart




#55 Do you have similar courses for Word, PowerPoint and other applications?

Posted by TheSmartMethod on 02 September 2013 - 04:34 PM

The only Smart Method courses that have been published as books/videos so far are:

 

• Learn Access 2003 VBA With The Smart Method ISBN-13: 978-0955459900

• Learn Excel 2007 Essential Skills with the Smart Method ISBN-13: 978-0955459924

• Learn Excel 2007 Essential Skills with the Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2007 Expert Skills with The Smart Method ISBN-13: 978-0955459931

• Learn Excel 2007 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459979

• Learn Excel 2010 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Expert Skills with The Smart Method ISBN-13: 978-0-9554599-8-6

• Learn Excel 2010 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Essential Skills with The Smart Method ISBN:  978-1-909253-06-3

• Learn Excel 2013 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Expert Skills with The Smart Method ISBN:  978-1-909253-07-0

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459962

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method ISBN-13:978-0-9554599-9-3

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Essential Skills with The Smart Method ISBN-13: 978-1-909253-04-9

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method ISBN-13: 978-1-909253-05-6

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

 

Eventually Mike Smart will write up his Access, Word, Project, PowerPoint… and other courses as books and videos but it will take many years to complete them all. The Smart Method are currently exploring ways of increasing productivity by employing proof-readers, testers and video editors to free more of Mike’s time for course design, writing and recording.




#949 Advice for Intro College Course

Posted by Mike on 09 May 2014 - 06:31 PM

Dear John

 

Pivot Tables are most definitely an Expert skill.  I’ve taught Excel classes to corporate executives for over ten years and it is rare to find any Excel business users that understand Pivot tables.   This skill is even rare amongst professional accountants.

 

Perhaps the first step would be to establish whether your students really need Pivot Table skills.  These skills are mainly used to analyze de-normalized data extracts from relational databases rather than to analyse data that has actually been input into Excel.

 

Assuming that your students really do need pivot table skills they should also be able to understand tables.  Since Excel 2007 was released,  tables (rather than ranges) are best practice for the data source underpinning a pivot table.  Tables are covered in Session One: Tables and Ranges (Expert Skills) 

 

As a minimum it would be possible to proceed directly to Session Five: Pivot Tables of Expert Skills after digesting Session One: Tables and Ranges of Expert Skills but many of the functions taught in Session Three: Advanced Functions of Expert Skills are often needed in order to prepare the data table (when extracted from a relational source) prior to creating a pivot table from it.

 

Excel 2013 has also added a new spin to Pivot Tables with the introduction of the OLAP Pivot Table and the data model.  There’s an entirely new session in 2013 Expert Skills devoted to this subject Session Six: The Data Model, OLAP, MDX and BI that also covers relational database theory (essential to the understanding of OLAP Pivot Tables).  I’d be surprised if even one percent of Excel business users, now and in the future, will ever have any understanding of OLAP (though it is tremendously useful when working with relational database data sources).

 

When I teach my Excel classroom courses it is rare to have a student who has no prior exposure to Excel.  For this reason it is usually possible to “cherry pick” lessons from different sessions based upon existing skills and the need for certain features.   This approach only works with small classes with similar prior experience (my maximum class size is six).

 

Essential Skills is designed to give students excellent Excel Skills (skills that will put them ahead of the average office worker and an employer’s expectations for “good” Excel skills).  Expert Skills covers just about every Excel feature and also provides the skills to use any other feature that isn’t explicitly covered.  These are skills that are not needed or expected in most occupations.

 

Best Regards

 

 

Mike Smart




#843 Conditional Formatting

Posted by Jonathan on 19 March 2014 - 10:54 PM

Hi Phil,

 

It's quite easy to achieve this if you have an understanding of how dates and times work in Excel.  The following formula will get the number of hours between the current date and time and the time of the complaint (assuming this is in cell A6):

=(NOW()-A6)*24

 

To understand why this formula works, see the video lesson: Expert Skills Lesson 3-7: Understand date serial numbers.

 

Now that you have the number of hours that you need, it's a simple matter of applying conditional formatting and customizing it to display the "traffic lights" at the values you need.

Customizing conditional formatting is covered in depth in the video lesson: Essential Skills Lesson 4-15: Use simple Conditional Formatting.

 

I have attached an example spreadsheet with the formulas and conditional formatting applied:

 

Attached File  TrafficLights.xlsx   9.56KB   297 downloads

trafficlights.png

 

If you need any more assistance with this, please feel free to reply.




#719 VLOOKUP using text string?

Posted by Jonathan on 18 February 2014 - 04:53 PM

The problem is that VLOOKUP always looks in the first column of the range that you specify.

 

Your formula is:

=VLOOKUP(G2,'[Sales First Quarter 2008-3.xlsx]January'!$A:$J,9,FALSE)

 

The problem here is that you are telling your VLOOKUP function to look at columns A:J in the Sales First Quarter 2008-3 workbook.  VLOOKUP will look in the first column of the table to try to find the product name, so it is trying to find the product name in column A.

 

For a more in-depth explanation of how VLOOKUP works, I recommend viewing Expert Skills Lesson 3-22: Use a VLOOKUP function for an exact lookup.

 

To get this to work, your formula should be something like:

=VLOOKUP(G2,'[Sales First Quarter 2008-3.xlsx]January'!$G:$I,3,FALSE)

 

This formula looks in column G for the product name and then returns the unit price from column I.

 

When using VLOOKUP, it's usually better to create a table that's specifically designed for lookups.  I've attached a working example:

 

Attached File  Sales First Quarter 2008-3.xlsx   34.27KB   1450 downloads

xlookup1.png

 

Attached File  Sales First Quarter 2008-Prices.xlsx   10.02KB   1150 downloads

xlookup2.png

 

If you continue to have problems, please feel free to reply.




#2757 Excel practice

Posted by Mike on 13 August 2017 - 04:19 PM

Greetings

 

The construction kit book is complete but we're putting 100% of our resources into new editions of the Excel 2016 book to support the new September 2017  version (Excel 2016 is now updated twice each year with a new semi-annual version and we're comitted to keeping the book updated as each new version is released).  Just as soon as we have the new book editions published we'll be turning our attention to the construction kit book.  My best guess is mid to late September at the moment.  Once the first construction kit book is finalized we have many more ideas for similar books.

 

Best Regards

 

 

Mike Smart




#2599 Formula for adding a date to an invoice

Posted by Jonathan on 17 January 2017 - 09:20 PM

Hi JaseandShell,

 

Yes, you can use the TODAY function to place the current date in a cell, or use the NOW function for the current date and time.

 

You can see more about how to use these functions in the video lesson: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

You can also use insert the current date or date and time without using a formula, using the keyboard shortcuts <Ctrl>+<;> and <Ctrl>+<:> (Control-Semicolon and Control-Colon). The date won't automatically refresh if you use the keyboard shortcut.




#2187 Validate negative values

Posted by Jonathan on 08 February 2016 - 07:38 PM

Hi LT27,

 

I am not completely sure that I understand what you're trying to do, but it sounds like you want to check whether the Awarded Savings value is negative and, if so, display 0 in that cell. In the same way, you want to display Cost Savings if the Awarded Savings is above zero, and Cost Avoidances if not.

 

Both of these can be achieved by using an IF function to check whether the value is below zero.

 

The first formula will look like this:

=IF(B12-B13<0,0,B12-B13)

 

The second will look like this:

=IF(B12-B13<0,"Cost Avoidances","Cost Savings")

 

If you're having trouble understanding these formulas, you can find a complete explanation of the IF function in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

 

I also noticed that you might want the values to appear in red if there are negative savings. You can make this happen automatically by using a formula-driven conditional format.

You can learn more about these in the video tutorial: Essential Skills Lesson 4-18 Create a formula driven conditional format.

 

I have attached a copy of your workbook with the new formulas and conditional formatting applied:

 

Attached File  Test 1.xlsx   10.88KB   75 downloads

Test 1.png

 

I hope this is what you were looking for, but please feel free to reply if you're still not sure how to proceed.




#2149 Is there a way to concatenate multiple cell formulas (instead of resulting va...

Posted by Jonathan on 25 January 2016 - 09:28 PM

Hi bennyb,

 

The FORMULATEXT function will extract the formula from a cell and return it as text. For example =FORMULATEXT(A1) will show the formula that is in cell A1.

 

Your second requirement isn't quite as straightforward, but you should be able to achieve what you need by using the INDIRECT function. INDIRECT will convert text into cell references that will work in formulas, but it won't accept an entire formula.

 

You can learn all about INDIRECT in the video tutorial: Expert Skills Lesson 4-5 Use intersection range names and the INDIRECT function.

 

I've also attached an example workbook:

 

Attached File  INDIRECT.xlsx   9.59KB   114 downloads

INDIRECT2.png

 

I hope this is what you were looking for, but please feel free to reply if you need any more help with this.




#2062 Project 3 years out from date entered minus 1 day

Posted by phoenixrizing on 02 December 2015 - 06:06 PM

Usually, it's the formatting I have trouble with when working with dates. I don't think this is the case now though. It's more an issue with calculations.
 
I want a user to enter a starting date. I will then calculate three years out from that date. This 3-year period is a cycle, so the end date should be one day prior to the start date three years out.
 
Example: If I enter June 23, 2014 in cells E3, F3, G3, the end date should be June 22, 2017 in cell E4.
 
The problem I'm having occurs when I enter a year beyond 2015. When I do this, the projected date goes out three years into the future as it should, but the date remains unchanged. When selecting 2015 and previous years, it seems to work fine.
 
Additionally, I need to account for the start date occurring in one month and the end date being in the prior month while taking into account leap years.
 
Attached File  date forecast.xlsx   14.1KB   118 downloads



#1752 analyzing discontinuous number strings

Posted by Jonathan on 08 July 2015 - 04:06 PM

Hi David,

 

While you could produce a correct result using Excel, this sounds like a problem that could be more suited to programming as it may be a very laborious process to create all of the formulas that are needed.

 

If you do want to proceed using Excel, you should be able to extract the strings by using Excel's FIND function along with several other functions including LEN and MID.

 

I have attached an example workbook showing how this might be achieved:

 

Attached File  StringSplit.xlsx   11.1KB   199 downloads

StringSplit.png

 

As you can see, the first thing I have done is to concatenate all of the values together into a single string using the concatenation operator (&). This makes it possible to use the FIND function on the resulting string.

You can find a more in-depth look at how concatenation is done in the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

Next, I extract the first string using the following rather complicated formula:

=MID([@Concatenated],FIND("0",[@Concatenated]),FIND("1",[@Concatenated],FIND("0",[@Concatenated]))-FIND("0",[@Concatenated]))

 

This formula finds the first zero in the string and the first 1 in the string and then uses the MID function to extract the characters between them.

 

In order to find the correct place to start to find the next string, the following formula is used:

=FIND([@String1],[@Concatenated])+LEN([@String1])

 

This simply searches for the string in the text and adds the length of the string to the result.

 

You can find more about the FIND and LEN functions in the video lesson: Expert Skills Lesson 3-21 Extract text from delimited strings using the FIND and LEN functions.

You can learn more about the MID function and other string handling functions in the video lesson: Expert Skills Lesson 3-20 Extract text from fixed width strings using the LEFT, RIGHT and MID functions.

 

Now the process can be repeated as many times as are needed to extract all of the strings. In my example I have also added an IFERROR function to return a blank result if there are no more strings to extract. You can find more about IFERROR in the video lesson: Expert Skills Lesson 3-23 Use an IFERROR function to suppress error messages.

 

Now that the strings are extracted, it's a simple matter to find their lengths using the LEN function and discover the longest, shortest and average using the MAX, MIN and AVERAGE functions. These are covered in the video lessons in Essential Skills Session 2: Doing Useful Work with Excel.

 

I hope this is what you were looking for, but please feel free to reply if I can offer any further assistance.




#1599 Function Keys

Posted by Jonathan on 09 April 2015 - 08:26 PM

Hi Annie,

 

It sounds as though your keyboard is allowing secondary purposes for your F2-F8 keys by using the Fn key.  This is a trend that started with laptops in order to keep keyboards compact, but some desktop computer manufacturers have started to do this as well to enable quick access to special features.

 

It's usually the case that keys work normally unless you are holding down Fn, but many keyboards of this type have a "Fn Lock" feature that permanently changes their purpose until it is disabled.  I think you have enabled 'Fn Lock' and that is why your keys aren't working as they should.

 

Unfortunately, there is no common standard for this kind of keyboard so I can't offer any advice on how to disable this - it will depend on the manufacturer of your computer.  If the solution isn't obvious, you might be able to find an answer in the manufacturer's documentation or by contacting them directly.




#1498 Excel 2010 Rounding Decimals

Posted by Mike on 01 March 2015 - 07:17 PM

Hi Phil

 

When you've advanced a little further through the course you'll discover how to show as few or many decimal places as you wish in the lesson:

 

Lesson 4-3: Format numbers using built-in number formats

 

But don't jump ahead in the course to session 4.  Just keep progressing lesson by lesson as knowledge builds upon knowledge as you advance through the course and new skills are introduced.

 

Best Regards

 

 

Mike Smart




#10 Forum Rules - Please read before posting

Posted by TheSmartMethod on 02 September 2013 - 02:05 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.

  • jon likes this