Formula Building.

[Spraggs]

Good Evening,
I have one column with a list of approx. 4000 machine tags.

Example.... 1234-ABC-DEF-GHI-AB123

The label can be anything from four digits to twentyfour digits (including "-")
The constant within the labelling are:
Dependant on label length there is always a "-" at the 5th, 9th, 13th , & 17th position.

So... assume the above machine tag is in cell A2, in cell B2 I want a formula that will give me a result 1234-ABC-DEF-GHI

Hence I want the formula to always remove the last "-" and anything after it.

Thanks Jase

 

[Jonmo1]

Try

=LEFT(A2,FIND("^",SUBSTITUTE(A2,"-","^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1)


Hope that helps.

 

[Desu Nota from Columbus]

Try: In B2
=Left(A2,16)


A2= 1234-ABC-DEF-GHI-AB123
B2= 1234-ABC-DEF-GHI


I think I grossly underestimated your question:

Did you want the last hyphen and alphanumeric set on all lengths to be cut-off? Or only the hyphen in the 17th position and onward removed?

 

[Jonmo1]

Updated to account for the 4 digit values that do not contain -

=IF(ISNUMBER(FIND("-",A2)),LEFT(A2,FIND("^",SUBSTITUTE(A2,"-","^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1),A2)

 

[Desu Nota from Columbus]

jonmo1's works with all except if there is not a hyphen.

proposed:

=IF(ISERROR(LEFT(A2,FIND("^",SUBSTITUTE(A2,"-","^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1)),A2,LEFT(A2,FIND("^",SUBSTITUTE(A2,"-","^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1))

 

[Spraggs]

Thanks Jonmo, perfect.

Desu, thank you but your sollution would only work if the tags were all the same length.

 

[Jonmo1]

jonmo1's works with all except if there is not a hyphen.

proposed:

=IF(ISERROR(LEFT(A2,FIND("^",SUBSTITUTE(A2,"-","^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1)),A2,LEFT(A2,FIND("^",SUBSTITUTE(A2,"-","^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1))

Good suggestion, but FYI...

You don't always have to test the entire formula for the error...
You only need to test for what causes the error, it is often much simpler..

In this case, the cause of the error is the string not containing a hyphen..

 

[Desu Nota from Columbus]

Good suggestion, but FYI...

You don't always have to test the entire formula for the error...
You only need to test for what causes the error, it is often much simpler..

In this case, the cause of the error is the string not containing a hyphen..

That makes a lot of sense (and makes the formula much more appeasing to the brain), but:

What is the difference between ISNUMBER and ISERROR? I thought that 1234-ABCD-5678-GHI would be a string (not a numerical value) therefore the ISNUMBER would return false whether or not a hyphen exists so the result of the IF would always return A2.

(I just realized that you were using ISNUMBER(FIND("-",A2)) as opposed to the entire formula. As you said before, search for what causes the error, it is often much simpler!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!)

 

[Desu Nota from Columbus]

EDIT*** I didn't realize the value of the ISNUMBER was a search for a hyphen...

This make complete sense to me now.

Thank you for the advice.

 

[Jonmo1]

That makes a lot of sense (and makes the formula much more appeasing to the brain).

What is the difference between ISNUMBER and ISERROR? I thought that 1234-ABCD-5678-GHI would be a string therefore the ISNUMBER would return false whether or not a hyphen exists.

Isnumber is testing the result of FIND..It's not looking at the string.

The Find function returns either:
1. A Number (the position # the value is found in the string),
2. An error (the value was not found)


You can use Either IsError OR IsNumber to do the test...
Just reverse the TRUE/FALSE results in the IF depending on which one you used.

=IF(ISNUMBER(FIND(...)),Do this, Do that)
=IF(ISERROR(FIND(...)),Do that, Do this)

I suppose the only real difference depends on the data type returned by the function you are testing for errors..
ISNUMBER can only be used if the return type you are expecting is a NUMBER.
It won't work on say a Vlookup that returns a text string.
Because ISNUMBER would never be true, even when there's no error.
In that case, you would use ISERROR, or even ISNA