Forum Discussion
Help with creating an Excel formula starting with a cell with text and number
Hello.
I am seeking help to create a simple formula to show ticket sales.
My starting cell has a description of the ticket category with the price of the ticket. The next cell, in the adjacent column the number of those tickets sold will be entered. I simply want to grab the number from the first cell and multiply with the number sold in a third cell. I have tried but I cant get around the text in the first cell and not an advanced user in Excel. Thank you in advance for your help.
You can use a number of functions for such a task:
Use FIND() to analyse your string.
Use LEFT(); RIGHT(); MID(); TEXTSPLIT(); TEXTAFTER(); TEXTBEFORE() to take the string apart.
You then use the VALUE() function to turn the text into a value.
To help you more precisely, you would need to know the contents of the cell as an example.
- Thanks. This is an example of the contents of the cell: Early Bird Ticket Combo - Reception + Gala - Non-Member $335.00
I have several of these with different ticket types and prices
Thank you for helping me!!Both formulae lead to the same result. TEXTAFTER() has only been around for a short time.
In my example, the string with the price at the end is in cell A2. For further rows, you can simply copy the formula downwards.
=VALUE(TEXTAFTER(A2,"$")) =VALUE(MID(A2,FIND("$",A2)+1,10))If you perform the multiplication straight away, you can save yourself the VALUE().
