Another "how to" excel question

[WyrleyD]

Son-in-law has asked me to create a spreadsheet for him as I used to work in "computers" (many years ago I might add).

What I need to do is perform a calculation dependent on a partial string in a column A cell i.e. if the first 4 characters contain "101-" then it's one calculation and anything else it's another entirely different calculation.

There are about 500 rows in all containing some blank ones too.

I have no problem woking out the calcs but I can't work out the best function to determine the partial string. Excel seems to have got a lot richer since I last used it about 40 years ago and even then I was just a basic user for simple calcs.

Any pointers would be much appreciated.

Thanks in advance...

[PurpleTurtle]

There is no specific 'Contains' logic in Excel.

What I would do is use some of the logic you see here, to set what is effectively an indicator variable, in an adjacent column.

Then use some IF logic on the value of that indicator variable, to direct a subsequent calculation.

https://www.excel-easy.com/examples/contains-speci...

[paulrockliffe]

It likely doesn't matter much how you do it, the best method will be determined by other aspects of what the spreadsheet does, how it will be used etc.

The basic answer is = IF(LEFT(A1, 4) = "101-", Calc, Other Calc) but if you're doing anything more substantial, want to deal with duplicates, want to be able to update the data, then I would either format your data as a Table, or save it to a folder a s a.csv file, depending on where it comes from, and then load it into Power Query in Excel, which is much more powerful when it comes to cleaning data, creating these sorts of Custom Columns and generally sorting the data so it presents nicely. Once the data is sorted out, load it to the Excel Data Model and present it back using Pivot Tables and Charts.

[dannnd]

Is it always the first four characters? If so you could use a combined LEFT and IF function? For example =IF(LEFT(A1,4)="101-"),"True","False"). You could combine a number of IFs if you also had another condition (a bit clunky but works).

Edited by dannnd on Friday 4th November 10:38

[paulrockliffe]

Contains is a basic filter type in Power Query, so if your requirement involves the level of messing about in that link, I would 100% be in Power Query.

[WyrleyD]

Thanks all for your help. I did use the IF to test in the end for the substring and then used the resulting TRUE/FALSE result for the calcs and it seems to have all worked OK.

[Trevatanus]

WyrleyD said:

Thanks all for your help. I did use the IF to test in the end for the substring and then used the resulting TRUE/FALSE result for the calcs and it seems to have all worked OK.

And the son in law thinks you're a legend....

Pistonheads strikes again!