"." Period filter for wire number labels

[mariomp]

Hello, once again I have a difficult (for me) issue to figure out.

Is there a way to have a column with random width data with some fields having decimal point values, and have ability sort/filter so that data with decimal point doesn't show up?

Background:
I have a list of wire numbers in this format: xxx-xx-xx.x , where Room-Bundle-Wire.Leg
Most of labels are 8 or 9 positions long where we have IDs such as:
002-01-2
002-02-01

But I also have trunk or distribution #s that look like this:
Patch # Patch Name
006-01 Coax 8 Position block in SMC1
006-02 48 Port Network Patch Panel in SMC1
008-01 Home Theater 24 Port Cat5e Patch Panel

The only constant is that the decimal point separates the unique wire from a leg number.
When determining # of patch panels, or drops, I don't need to see the leg.

So I may have a wire that goes from 3nd bedroom room (203), 2nd drop (02), and is 5 wire in the bundle (05).
That would be 203-02-5 or 203-02-05

Anything that plugs into this wire (or jack) would get a .x extension.
So if this was a network wires, PC to wall jack might be 203-02-05.1
In equipment room, patch panel to network switch might be 203-02-05.2


When printing wire lists or drop lists, I would like to hide the leg numbers (not show any rows with *.x)

Any ideas?

Thanks,
Mario

[mariomp]

Ideally I would have a separate column just for the .x leg feeds and could just sort by that.
This however presents it's own set of challenges when it comes to printing labels and dealing with existing lists.

[bebo021999]

It is hard to see what to do. Can you upload a small sample with input and expected output data?

[FDibbins]

Is there a way to have a column with random width data with some fields having decimal point values

I see nothing there with decimals. For something to have real decimals, it 1st needs to be an actual value/number - what you havethere is all text
Your sample also doesnt really match what you describe - you indicate a format of "xxx-xx-xx.x", yet your samples are 002-01-2/002-02-01 no .x?

If you want to only show data up to the , you could try a helper with this...
=LEFT(A1,FIND(".",A1,1)-1)

[mariomp]

I see nothing there with decimals. For something to have real decimals, it 1st needs to be an actual value/number - what you have there is all text
Your sample also doesn't really match what you describe - you indicate a format of "xxx-xx-xx.x", yet your samples are 002-01-2/002-02-01 no .x?

If you want to only show data up to the , you could try a helper with this...
=LEFT(A1,FIND(".",A1,1)-1)

Ford, you're absolutely correct. I don't have numbers. I have a string using digits.
You're also right that it's not really a decimal point.
In this example: 203-02-05.2 I'm using a "." period to separate main wire run from shorter, legs of said wire as patch cables.

With that said, how can I use your formula as a filter?
Could I do something like "if period=true then Yes" or any other qualifier?
That way I can have a separate column that I can choose if I want to see lines with a string that contains a period or not.

[mariomp]

It is hard to see what to do. Can you upload a small sample with input and expected output data?

Absolutely.Wiring Example.xlsx
I've shorten the file significantly.
This example shows different string lengths (A2, A8, A15, A25) as well as the lines in question that I would like to sometimes omit in reports, printouts, etc.
Rows A77, A83, A84 are examples of the ones I would like to be able to omit (hide).

I just realized that I can use the "sort --> Text Filters --> Does Not Contain..." to filter out any lines that do not contain "." period.
If all else is too difficult to figure out or implement, I'll resort to this method.

[FDibbins]

In a separate column, if all you want is an indicator that the dot is there, maybe something like...
=IF(ISERROR(SEARCH(".",A1,1)),"No","Yes")

[mariomp]

In a separate column, if all you want is an indicator that the dot is there, maybe something like...
=IF(ISERROR(SEARCH(".",A1,1)),"No","Yes")

You are the MAN!
Thank you.