IF cell contains a certain item of text then display result

Hi Folks,

I'm trying to create a spreadsheet that will look to see if a cell starts with a certain number and then it should look within the cell to see if it contains a "-" (dash) and display a result depending on whether there is a dash in it or not.

A bit more in depth,
The data provided is normally an 8 digit number beginning with a 2 (e.g. 23456789), however, sometimes it can be a number that begins with a 3 and typically contains a (dash) followed by a further two digits (e.g. 34567890-12). I need to be able to search for anything that begins with a 3 and display anything before the dash in one cell and anything after the dash in another cell.
So far I've been using the below formulas to get this:

=IF(LEFT(B3,1)="3",LEFT(B3,8),"")
=IF(LEFT(B3,1)="3",RIGHT(B3,2),"")

However, some of the data I'm using appears to not have a dash and the digits that should follow it which is causing my formulas to show incorrect results. What I'm wanting to do is search in the cell to see if it contains a dash and if it does pull through the data from after that dash into the cell, but if it doesn't then enter "10" into the cell.

I've attached a copy of the spreadsheet I've been using.

Hope someone can help! and that this actually makes sense!

L.

Test.xls

=IF(B3="","",IF(AND(LEFT(B3,1)="3",NOT(ISERROR(FIND("-",B3,1)))),RIGHT(B3,2),""))

Maybe that for your G column.

Hi yudlugar,

Thanks for your prompt response. I've tried inputting that and while it works for the data that begins with a 3 and has no dash, it doesn't work for when the data does contain a dash and then when the cell is blank it is pulling through "10" when I would prefer it to stay blank...

Any other suggestions?

L.

I think maybe you have the cell row references "out of sync?"

See attachment as an example

Hi yudlugar,

That seems to have worked! Many thanks for your help!

L.

apologies yudlugar,

it doesn't appear to have worked afterall.

It is not automatically putting in the 10 if there is no dash...

L.

Sorry, didn't see that requirement
=IF(B3="","",IF(AND(LEFT(B3,1)="3",NOT(ISERROR(FIND("-",B3,1)))),RIGHT(B3,2),10))

No worries,

I've tried adding that in but it is now showing 10 all the time no matter if the original data begins with a 2 or 3 and has no dash... Ideally it should remain blank unless it begins with a 3 and there is no dash..

Sorry for being a nuisance!

L.

=if(and(b3="",not(left(b3,1)="3")),"",if(iserror(find("-",b3,1)),right(b3,2),10))
?

Sorry still no luck with that one.

/l.

Last go:
=IF(OR(B3="",NOT(LEFT(B3,1)="3")),"",IF(ISERROR(FIND("-",B3,1)),10,RIGHT(B3,2)))

I'm pretty sure that's it now!

Thank you very much!