Extract text between a character

[Jake_1]

Looking for a formula that allows you to extract text between two characters.

As an example, here we have the name of somebody, and how many whatever's they have.
";Jamie;3;Sarah;8;Simon;24;Lauren;5;"

Then using this formula, the data would be put into a table. |Name|X of whatever's|
Of course for what I'm really using this for, I don't want to just enter the data into a table because of formatting reasons.

[Pete_UK]

Can't you just use Data | Text-to-columns with ; as the delimiter?

Hope this helps.

Pete

[Jake_1]

It does help however I found that still isn't exactly what I need.

Example. At the moment I'm just using text to columns to convert something such as, "Small Laser;3" into "| Small Laser | 3 |".
As I'd rather not have another table or something with expanded information on, I need a formula that checks and matches the "Weapon Name" to a list of other weapons. It then needs to (I assume) VLOOKUP that weapon, find it's damage per a second value then times it by the "Quantity" value.

|Weapon Name|Quantity|Damage Per Second |
|Small Laser |3 |This is where I need a formula|

(the text that the table works off "Small Laser;3" isn't always the same amount of characters long, or the same order. I.e it could be "Large Laser;1;Small Railgun;8;Medium Cannon;3)

[AliGW]

Provide a set of sample data showing what you want the formula to return. 10-12 rows of representative data should be enough.

[Jake_1]

Screenshot_4.png
Wasn't able to attach a document.

[AliGW]

Can't work with a picture - sorry!

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Jake_1]

Thank you.

[AliGW]

Thanks. Which are the expected results that I asked you to add manually?

[Jake_1]

Ah sorry, completely forgot. The expected results would be X*However many turrets are present. I.e Advanced Miner has 4 small mining lasers. So the end result would be 4*2.5

2.5 being the value for one small mining laser.

[AliGW]

Please add them manually to your workbook and attach again. You know what you want - I'm struggling to visualise it. Help us to help you!

[Jake_1]

There we go.

[Pete_UK]

It is easier to split the multiple turrets into separate columns, so I have inserted 3 new columns and split column B with Text-to-columns using ; as the delimiter, as previously advised. I changed the headings for the new columns, and then you can use this formula in column F:

=Table3[[#This Row],[QTY_1]]*VLOOKUP(Table3[[#This Row],[Turret_1]],Table2,2,0) + IFERROR(Table3[[#This Row],[QTY_2]]*VLOOKUP(Table3[[#This Row],[Turret_2]],Table2,2,0),0)

The attached file shows the result.

Hope this helps.

Pete

[Jake_1]

The problem with that is each ship can have up to 11 different weapons. So having 22 columns (plus the rest that I'm going to add in) is unnecessary. Having the formula that I'm looking for would significantly reduce that number.

[Pete_UK]

But your formula would become very long (and difficult to maintain). For example, for those records where you only have one weapon, then you could use this formula:

=MID(Table3[[#This Row],[Turret(s)]],FIND(";",Table3[[#This Row],[Turret(s)]])+1,1) * VLOOKUP(LEFT(Table3[[#This Row],[Turret(s)]],FIND(";",Table3[[#This Row],[Turret(s)]])-1),Table2,2,0)

Essentially, the MID part of this formula finds the number (assumes 1 character after the semicolon, but maybe this could be 2), then the VLOOKUP gets the appropriate value from Table2 for the weapon found on the left hand side of the semicolon.

Where you have two or more weapons this wouldn't work, as you would need to find where the second semicolon is in order to extract the first weapon;number, and use that in the above formula, and then do this again for the second weapon;number pair, and repeat for up to 11 weapons.

I'd certainly go for a simpler data layout and simpler formula - you could always hide the extra columns to retain the more condensed layout.

Hope this helps.

Pete