Microsoft Excel – first x characters of a text string but cutting off at full word

I was working with a client recently who had long descriptions of products within cells in Excel. To upload the descriptions to the web site, they could only use 80 characters but wanted to ensure that there wasn’t part of a word at the end of the string.

I have to confess I ended up doing this in two stages – there may be an easier way but it works!

I have input some text as shown above.

In cell B1, I have found the first 80 characters by using the LEFT function

=LEFT(A1, 80)

I then copy down the formula for as many rows as there are in my worksheet.

The formula in cell C1 to cut off at the end of the last full word is rather more complicated:

=LEFT(B1,FIND(“/”,SUBSTITUTE(B1,” “,”/”,80-LEN(SUBSTITUTE(B1,” “,””))),1))

The SUBSTITUTE part of this looks for spaces and substitutes this with a / but only at the last instance of the space.

LEN looks at the length of a string.

The LEFT function then takes B1 and cuts it off at the /.

FIND returns the starting position of one text string within another text string, so in this instance looks for how many characters there are to the / then the LEFT function returns the characters up to that position.

Not terribly easy I admit but it does work!

Find out more information about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Access to Microsoft Word mailmerge – dates in wrong format

I was using data from a Microsoft Access query in a Microsoft Word mailmerge document the other day. However on returning dates they were in the American format rather than the UK one, although both Microsoft packages were showing my language as English UK.

I was very keen that the dates went out in UK format.

To change it, right click on the Date itself, then click Edit Field. The Field dialog box will be displayed.

Ensure MergeField is selected in the Field names section and that the relevant date field is selected in the Field name box.

Click Field Codes. In the Advanced Fields section type \@”dd/MM/yyyy” immediately after the field name.

Click OK. Your date will be formatted correctly.

Find out more about our Microsoft Access training in the London area or our Microsoft Access training in Wiltshire, Dorset, Somerset and Hampshire

Posted in Microsoft Access, Microsoft Access 2013, Microsoft Access 2016, Microsoft Word, Microsoft Word 2013, Microsoft Word 2016 | Tagged , | Leave a comment

Microsoft Outlook – Ribbon not showing all icons

One morning I opened Microsoft Outlook to see only a condensed version of the ribbon I am used to seeing.

I thought this may be something to do with a Microsoft upgrade but this isn’t really the case. The ribbon is collapsed – it is known in Microsoft parlance as the Simplified Ribbon. At the bottom right of the ribbon is a downward facing arrow, This gives you the option to toggle between the Simplified Ribbon and the Classic Ribbon.

Find out more about our Microsoft Outlook training in the London area or our Microsoft Outlook training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Microsoft Office 365, Microsoft Outlook, Outlook 365 | Tagged , | Leave a comment

XLOOKUP – finding first or last value in data set

Yet another blog about the XLOOKUP function which is so powerful.

Say I have a list of customers and I want to find the date of their first order or their last order.

I have more than one order for several customers.

The syntax for the XLOOKUP function is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. 

It is the Search mode that we need to think about in this instance. This is input as:

1 – Perform a search starting at the first item. This is the default.

-1 – Perform a reverse search starting at the last item.

2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

In the example I am using my Order Dates are in sort order; if not, you would need to sort before running the XLOOKUP. To find the earliest Order Date, we use 1 as our search mode and to find the latest Order Date we use -1 as our search mode.

So my first formula is =XLOOKUP(I2,B2:B154,D2:D154,,0,1)

and my second formula is

=XLOOKUP(I2,B2:B154,D2:D154,,0,-1)

Other examples would be finding the first or last sales amount for a particular client, etc.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Excel 365 | Tagged , , | Leave a comment

XLOOKUP – finding the number of orders between two dates

We have already looked at using XLOOKUP to return data where columns are not in a contiguous order.

There are many other uses of the XLOOKUP function. One could be to return the number of orders between two dates or the value of orders between two dates.

In the spreadsheet shown we have order dates and individual orders. Say, I wanted to find how many orders came in in July 2020.

I have added the required Start Date in cell I2 and the required Finish Date in cell J2.

The relevant combination of the functions COUNT and XLOOKUP in cell K2 would be:

=COUNT(XLOOKUP(I2,D2:D154,A2:A154):XLOOKUP(J2,D2:D154,A2:A154))

Similarly, I could use the SUM function on a relevant column to find the total value of orders.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Excel 365 | Tagged , , | Leave a comment

Microsoft Excel – XLOOKUP function – returning data from non-contiguous columns

In my recent blog on the XLOOKUP function I showed how useful it was rather than using the VLOOKUP function.

When training XLOOKUP recently, I was asked if it could return data on columns that were not next to each other.

Yes, you can, but it also involves using the CHOOSE function.

In the example below, the original column order was Wine Number, Wine Name, Quantity and Price:

I want to return Wine number, Price and Quantity – wine name Semillon 2009 is in cell G13, so I am putting my cursor in cell H13.

Going to XLOOKUP, the relevant syntax is

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],[match_mode],[search_mode])

In this example, we need cell G13 for the lookup value and $B$2:$B$10 is the lookup array.

It is the return array that is more tricky. The CHOOSE function allows us to specify the order of columns so I need CHOOSE({1, 2, 3}, $A$2:$A$10, $D$2:$D$10, $C$2:$C$10). We want to return 3 columns, that is why the CHOOSE function has the 1,2,3; the squiggly brackets indicate that we are returning an array of three columns.

So my formula is:

=XLOOKUP(G13,$B$2:$B$10,CHOOSE({1,2,3},$A$2:$A$10,$D$2:$D$10,$C$2:$C$10),”Not in stock”)

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Excel 365, Microsoft Excel, Uncategorized | Tagged , , | Leave a comment

Microsoft Excel – adding company logo to header

When printing off an Excel spreadsheet, or indeed emailing it to a client, you may want to include your company logo as part of the header.

To insert a header, click the Insert tab, then in the Text group, click Header & Footer. (Alternatively, click on the Page Layout icon to the right of the Status Bar.).

Click in the Header section, in the area where you want to insert the icon, then on the Header & Footer tab, and in the Header & Footer Elements group, click Picture. Locate the logo you want to insert, then click Insert. Until you move your cursor away from the area you inserted the logo, all you will see is &[Picture].

Move your cursor to another part of the header and your logo will be revealed. It may well be too large, in which case, click on your logo again, then in the Header & Footer Elements group, click Format Picture. The Format Picture dialog box will be displayed. Click on the Size tab and select a suitable % in the Height and Width boxes. Click OK.

It may then be the size you require but still be covered by some of the data in your worksheet. In that case, go to File – Print and click Page Setup, then go to the Margins tab. Make the Top margin bigger, then click OK. This should mean you can see both logo and data appropriately.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged | Leave a comment

Microsoft Excel – I get the same answer in every cell when I copy my formula down

I was training Excel and we were doing an IF statement in one cell and then copied down to the other cells in that column. One in the session got the same answers for all cells though looking at her formulas they all looked correct.

I then asked her to go to the Formulas tab and show me what was under the Calculation Options dropdown in the Calculation group. For some reason, Manual was ticked which meant that she would have to click Calculate Now to get the calculation to work. Sometimes, with very big workbooks, manual calculation saves processing time.

To change the default option, from the Calculation Options dropdown, click Automatic. Alternatively, go to File – Options, then select the Formulas tab. Select Automatic in the Workbook Calculation section, then click OK.

Formulas will then be automatically calculated.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Uncategorized | Tagged , | Leave a comment

Microsoft Excel – XLOOKUP

If you are using Excel 365 or Excel 2019, you will be able to use the new XLOOKUP function. This has many uses – I am outlining one of them here.

Previously if I was using a VLOOKUP function and wanted to use the function across several columns, I would use a VLOOKUP function combined with a COLUMN function. And if I also wanted to add in a message if there was no match, I also included the IFERROR function.

So if my original date looked like

and I had a list of Wine numbers that a client had asked for and I wanted to find the relevant Name, Quantity and Unit Price, my formula, in cell H2 if the Wine number that I was trying to locate in this data set was in cell G2 would be:

=IFERROR(VLOOKUP($G2,$A$2:$D$10,COLUMN(B1),FALSE), “Not in stock”)

To achieve a similar result with an XLOOKUP, the formula would be:

=XLOOKUP(G2,$A$2:$A$10,$B$2:$D$10,”Not in stock”)

Which is rather easier. When I use this in cell H2, it automatically also fills in I2 and J2 as it is looking at the information in 3 columns, B to D.

The syntax for the XLOOKUP used here is

=XLOOKUP(lookup value, lookup array, return array, if not found)

There are other options for closest rather than exact match, etc, but I’ll look at them at another time!

Find out more about our Excel training in the London area or our Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Excel 365, Microsoft Excel | Tagged , | Leave a comment

Microsoft Outlook – left Caps Lock on by mistake?

It is very easy when typing an email in Microsoft Outlook to suddenly find that you have been happily working away with Caps Lock on by mistake. The last thing you want to do is delete everything you have done and start again!

Select the text that is in the wrong case, then on the Format Text tab, in the Font group, click the Change Case button dropdown.

toggle case dropdown

Select tOGGLE cASE. All upper case letters will become lower case and all lower case letters will become upper case. Problem solved!

Find out more about our Microsoft Outlook training in the London area or our Microsoft Outlook training in Wiltshire, Dorset, Somerset and Hampshire.

Posted in Microsoft Outlook, Microsoft Outlook 2010, Microsoft Outlook 2013, Microsoft Outlook 2016, Outlook 365 | Tagged , | Leave a comment