Formula Assistance

[Arts]

Hi all

I originally needed to grab evrything after the charcter "-" so for example
if i had 5855-Harry Crumb Plc I would want Just "Harry Crumb Plc"

I manage to come up with the formula =MID(B7558,SEARCH("-",B7558,1)+1,LEN(B7558)) after surfing through the net (I have no idea how the formula works but it seems to give me what i want i dont know what the +1 does....anyhow)

The problem I have is that when I attempt to drag the formula down any cell that doesnt have a "-" gives me #VALUE

Could anyone please provide me with a formula that I can drag down so if there is a "-" it returns me everything afer that and if there is no dash it just returns me the value in the cell

Many Thanks

 

[JamesW]

Hi,

Maybe try:

=IF(ISERROR(MID(B7558,SEARCH("-",B7558,1)+1,LEN(B7558))),B7558,MID(B7558,SEARCH("-",B7558,1)+1,LEN(B7558)))

Which says: If there's an error (no "-") then just return the value in cell B7558, otherwise carry on with your original formula.

I have no idea how the formula works but it seems to give me what i want i dont know what the +1 does....anyhow)

What it is doing is:

=MID(TEXT,START_NUM,NUM_CHARS)

So, it is returning the middle of your TEXT string (in cell B7558), starting at wherever it finds (SEARCH) a "-" and carry on to the length of the string (LEN).

The reason for the +1 is because it will search for where "-" is, which (in the example 5855-Harry Crumb Plc) is the 5th character, so the result would be "-Harry Crumb Plc" - as it is starting from where it finds the "-". Therefore we need a +1 to move onto the 6th character, giving us "Harry Crumb Plc".

 

[SteveO59L]

=if(iserror(find("-",a1)),a1,right(a1,len(a1)-find("-",a1)))

 

[Arts]

James/Steve thanks both for your formulas

James thank you for the explanantion regarding the +1 within the formula