Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excele

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Excele

Resolved · Urgent Priority · Version 2010

Reply Thu 3 Mar 2011, 17:50Delegate Roberto said...

Roberto has attended:
Excel Advanced course

Excele

Hello there

I have attended a Excel Advanced course with you 10 days ago
I made this course also because of the following problem

To make myself undeerstood I would need to send you and attachment
This attachment is an excel report exported from google analytics which details the visits to different pages of our website
We rent villas; and each villa ha a description which is split in 5 sections

So each villas have 5 different type of pages (separate urls)
• _CardID
• _PicturesID
• _PriceID
• _DescriptionID
• _CommentsID

I need to group these 5 types in just one; which is the name of the Villa
• This name is “contained” in the column A of first sheets between the text “/Rentals/Villas/” and one of the 5 text above: “_CardID
” etc etc

For example
/Rentals/Villas/La_Trappola_CardID_4663.aspx 1542
/Rentals/Villas/La_Trappola_PicturesID_4663.aspx 1509
/Rentals/Villas/La_Trappola_PicturesID_3840.aspx 1473

I need to group below 3 rows in just one. Named La_Trappola with a total of 4524

I was trying to do this using the VLOOKUP function
But I cant!! 

Can you please help

Many thanks

Below my delegate login details

Username: robertolucci@hotmail.com
Password: 6117

For upcoming training course dates see: Pricing & availability

Reply Mon 7 Mar 2011, 16:56Trainer Anthony said...

RE: excele

Hi Roberto, thanks for your query. What you're trying to achieve is possible but requires a combination of tools. When you have text or values within a string (in your Column A, I think) you are going to have to "parse" the data to lift out the information into separate fields. You can do this quickly with the text to columns tools in Excel. If you want to lift the name out of the strings cited above, you'll need your text formula functions (LEFT, MID, RIGHT etc).

Then when you have the data in separate fields, you can apply your grouping tools, which will also allow you to subtotal the numerical values. Have a go and see how you get on, if you need any resources regarding the tools I have mentioned drop me a line on the forum.

Hope this helps,

Anthony

Reply Tue 8 Mar 2011, 15:09Delegate Roberto said...

RE: excele

Hello Anthony
thanks for your help
I was actually thinking about the MID function
but the trouble is that the name of villa to be extracted from a longer text has a variable number of charcaters
In the example above i know that the name of the villa starts a position 17 but i dont know when it ends; as this depends on the lenght of villa name
is there any way around?

thank you

roberto

Reply Wed 9 Mar 2011, 11:33Trainer Anthony said...

RE: excele

Hi Roberto.

You need to count the number of letters in the string (=LEN(A1))
You should have a fixed number of letters at the end of the string (e.g. _4663.aspx has 10 characters). Add this to the position of the character to want to start from in the MID formula, this gives you 27 characters you want to lose from the overall string, however long that string is:

Therefore for each cell:

=mid(a1, 17, LEN(A1)-27)

Hope this helps,

Anthony

Reply Wed 9 Mar 2011, 16:11Delegate Roberto said...

RE: excele

Hi Anthony
unfortunately the length of the string (eg urls number of charcater) is variable
thats because the name of the villa is variable;

any other idea?

roberto

Reply Wed 9 Mar 2011, 16:30Delegate Roberto said...

RE: excele

HI Anthony
I have a more precise rule that i have to follow that may help you

I need to extract from a longer string the part included between

The string “Villas/” (which terminates always at charcater number 16)

AND

The string “_CardID”

OR

The string “_DescriptionID”

OR

The string “_PicturesID”

OR
The string “_CommentsID”

OR
The string “_PriceID”

One of the string above starts at a variable point. And this is the problem

Is there any formula I could use playing with strings above?

Thank you

roberto

Excel articles

· Why Printing Needn't Be A Grey Area In Excel
· What Are Sparklines In Excel 2010?
· How To Use Conditional Formatting In Excel
· Microsoft Excel provides the building blocks of shared information
· Trust Excel To Protect Your Data

Please browse our web site to find out more about
excel computer training and other Microsoft training courses.

Excel tip:

Transpose text

You can transpose any range of cells, turning the columns into rows and the rows into columns. Just follow these steps:

Select the range.

Click the Copy button on the Standard toolbar to copy it to the Clipboard.

Select a cell outside of the range you copied.

Select Paste Special from the Edit menu.

In the Paste Special dialog box, click Transpose, then OK.

View all Excel hints and tips