how to extract three consecutive numerals

mifala.chris

I have made it work but is cumbersome and not entirely accurate as I have had to add tildes or apostrophes to the front of some equipment to get the desired outputs

The equipment ID column contains ID's that contain what is called a UPN which consists of three consecutive numerals. I want to extract those numerals for use in identifying project disciplines, responsible parties etc. while the numerals always come prior to a dash the number of characters and their makeup prior to them are not consistent. I am not worried about the electrical components as I have a column just for them as they contain a code within them to identify them which is matched to the UPN.

Sample Data

I am using trial and test columns to attempt to get this out the issue I have is I cannot get rid of the errors I receive and can only get the numbers for the first dash not the second

formula from Test column

=IF(AND(FIND("-", [Equipment ID]@row) > 3, ISNUMBER(VALUE(MID([Equipment ID]@row, FIND("-", [Equipment ID]@row) - 3, 3)))), MID([Equipment ID]@row, FIND("-", [Equipment ID]@row) - 3, 3), IFERROR(IF(AND(FIND("-", [Equipment ID]@row, FIND("-", [Equipment ID]@row) + 1) > 3, ISNUMBER(VALUE(MID([Equipment ID]@row, FIND("-", [Equipment ID]@row, FIND("-", [Equipment ID]@row) + 1) - 3, 3)))), MID([Equipment ID]@row, FIND("-", [Equipment ID]@row, FIND("-", [Equipment ID]@row) + 1) - 3, 3), ""), "TEST"))

formula from trial column

=IF(AND(FIND("-", [Equipment ID]@row) > 3, ISNUMBER(VALUE(MID([Equipment ID]@row, FIND("-", [Equipment ID]@row) - 3, 3)))), MID([Equipment ID]@row, FIND("-", [Equipment ID]@row) - 3, 3), IFERROR(MID([Equipment ID]@row, FIND("-", [Equipment ID]@row, FIND([Equipment ID]@row) + 1) - 3, 3), "test"))

would love to have this all work together and if possible add the electrical formula to it as well

electrical formula

=IFERROR(IF([Power Type]@row = "C", "605 Continuous Power", IF(AND([UPN3]@row > 100, Heirarchy@row > 5), INDEX({Metadata - UPN & UPI List 'Sys Name'}, MATCH([UPN3]@row, {Metadata - UPN & UPI List 'UPN'}, 1)), IF(CONTAINS("-HT", [Equipment ID]@row), "610 Heat Trace", IF([Power Type]@row = "E", "604 Emergency Power", IF(AND([Power Type]@row = "N", Heirarchy@row > 2), INDEX({Metadata - UPN & UPI List 'Volts"}, MATCH([Voltage identifier]@row, {Metadata - UPN & UPI List 'Voltage'}, 0)), IF(AND([Power Type]@row = "X", Heirarchy@row > 2), INDEX({Metadata - UPN & UPI List 'Volts"}, MATCH([Voltage identifier]@row, {Metadata - UPN & UPI List 'Voltage'}, 0)), "")))))), "")

thanks in advance for the assistance

Paul Newcome

Ok. Assuming you don't have any "~" present in the regular data, you could use this:

=IFERROR(IFERROR(IFERROR(VALUE(MID([Equipment ID]@row, FIND("-", [Equipment ID]@row) - 3, 3)), VALUE(MID([Equipment ID]@row, FIND("~", SUBSTITUTE([Equipment ID]@row, "-", "~", 2)) - 3, 3))), VALUE(MID([Equipment ID]@row, FIND("~", SUBSTITUTE([Equipment ID]@row, "-", "~", 3)) - 3, 3))), "")

If you do have any "~" that could be present in the regular data, swap out all four of those in the above for a character that will definitely not be found in any of the strings.

Paul Newcome

Try this:

=IFERROR(MID([Equipment ID]@row, FIND("-", [Equipment ID]@row) - 3, 3), "")

mifala.chris

Hi Paul, that does not work for all rows because of the variations Like I said I can get the majority of them right with the first formula and the rest with the second formula I just can't figure out how to put them together.

The third formula I use in another column to get the same results from the code in them.

Chris

Paul Newcome

Did you try my formula? My formula ignores leading characters and is based off of finding that first hyphen. It doesn't matter if there are zero, one, two, or even two thousand characters before those first three numbers so long as those first three numbers are immediately followed by the first hyphen.

Is there some variation where the three numbers you want to pull are not immediately followed by the first hyphen?

mifala.chris

Paul, there are three differing test strings in the primary column which is the [Equipment ID].

there are instances where it finds the first hyphen however it returns the prior characters and they are not always numeric e.g

F27A1-CMS261-1-14 returns 7A1 instead of 261 which is the desired output. and this is where I started on this quest so I began testing for numeric value etc and looking for the second hyphen but not returning text. There are also some IDs where the three numerical characters are actually after the third hyphen e.g.

OHBC1-TOS-CH126-205B-10 numerals to return are 126

Chris

Paul Newcome

Ah. Ok. I see now.

We may still be able to leverage my initial formula as a base though and use a VALUE function to try to convert the string into a number. If there are any letters in it, it will throw an error. From there we use an IFERROR to tell it to use similar logic on the three characters before the second hyphen and then rinse and repeat for the three characters before the third hyphen.

While I work on the base idea of this… Are there any times where it could be the three characters before the 4th, 5th, etc. hyphen, or do we just need to look through the first three?

In those instances where there is no hyphen such as on parent rows, what do you want to output (or leave blank)?

mifala.chris

No three is the maximum number of hyphens, just adding that I get errors for rows that carry the +/- indicator for indenting as well. Must say its driving me nuts right now I had got quite good at find text/ numerical values but this one keeps biting me or making me use huge formulae so I really appreciate the help

Chris

Paul Newcome

Ok. Assuming you don't have any "~" present in the regular data, you could use this:

=IFERROR(IFERROR(IFERROR(VALUE(MID([Equipment ID]@row, FIND("-", [Equipment ID]@row) - 3, 3)), VALUE(MID([Equipment ID]@row, FIND("~", SUBSTITUTE([Equipment ID]@row, "-", "~", 2)) - 3, 3))), VALUE(MID([Equipment ID]@row, FIND("~", SUBSTITUTE([Equipment ID]@row, "-", "~", 3)) - 3, 3))), "")

If you do have any "~" that could be present in the regular data, swap out all four of those in the above for a character that will definitely not be found in any of the strings.

mifala.chris

Thanks Paul that worked exceptionally well. Now I can input that into my other column and delete multiple check columns I was using. The issue I am fast approaching is that the rows may grow to around 20,000 once this list of equipment is built out completely.

Again appreciate your help and have a blessed Day

Paul Newcome

Happy to help. 👍️