Getting cells containing numbers and letters in a form to make c

Resolved · Medium Priority · Version 2016

Tue 8 May 2018, 16:10 Delegate Ross said...

Ross has attended:
Excel Introduction course
Excel Intermediate course

Getting cells containing numbers and letters in a form to make c

I have a column of horses' starting prices written in the traditional format: i.e.16/1, 10/11, 100/30 etc etc. I've used 'text to columns' to allow me to express these as probabilities, by separating the two numbers and creating the calculation (B/A+B)*100, where A and B are the two numbers.
This is all fine. My problem is that when the horse is a Favourite or a co-Favourite this is written as 3/1F, or 10/11CF. I can't use the 'text to columns' as the letters appear in different positions depending on the numbers preceding them (as in the two examples above).
Help please!
Ross

Wed 9 May 2018, 09:07 Trainer Jens said...

RE: getting cells containing numbers and letters in a form to ma

Hi Ross,

Thank you for the forum question.

You have a number of solutions, but the most efficient and less complicated is to use Flash Fill.

In the column next to your 3/1F, 10/11CF, 100/30 type a heading press Enter and type the first number from the column to the left you want to extract (3 if it is 3/1F, 10 if it is 10/11CF). Press enter and click Flash Fill from the Data tab in the Data Tools group.

In the next column ad a heading, press enter and type the second number you want to extract (1 if it is 3/1F, 11 if it is 10/11CF) press Enter and again click Flash Fill.

I hope this makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 9 May 2018, 13:24 Delegate Ross said...

RE: getting cells containing numbers and letters in a form to ma

Thanks very much Jens

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Closing Multiple Workbooks quickly

When you have several workbooks open in Excel and want to just close them all at once:

1) Hold down the SHIFT key before selecting the File menu.

2) Once in File menu release SHIFT key and select Close All option.

3) All your files will close. If files require saving Excel will ask if you want to save the changes.

View all Excel hints and tips