Masking Numbers

[Ben51]

I have a set of numbers in column B

	07.01.01.01.01 <tbody> </tbody>
	07.01.01.03.01.01 <tbody> </tbody>
	07.01.01.04.01 <tbody> </tbody>
	07.02.01 <tbody> </tbody>
	07.02.04.04 <tbody> </tbody>

<tbody>
</tbody>

I would like a fill formula to fill column A and reduce the numbers in column B to '07.01.01.01' in length unless the number is already that length or smaller. in which case i would like the fill formula to knock off the last bit

So column A would look like this

07.01.01.01	07.01.01.01.01 <tbody> </tbody>
07.01.01.03	07.01.01.03.01.01 <tbody> </tbody>
07.01.01.04 <tbody> </tbody>	07.01.01.04.01 <tbody> </tbody>
07.02 <tbody> </tbody>	07.02.01 <tbody> </tbody>
07.02.04 <tbody> </tbody>	07.02.04.04 <tbody> </tbody>

<tbody>
</tbody>

Thanks in Advance Guys!

 

[steve the fish]

What does this bit mean 'to '07.01.01.01' in length unless the number is already that length or smaller'?? It seems you have just trimmed off the last 3 characters to me.

 

[anglais428]

For the first 3 examples you could just use =LEFT($B1,11) and fill down.

Why does 07.02.01 become 07.02? And why does 07.02.04.04 become 07.02.04?
There has to be some additional criteria for this in addition to what you have specified?

 

[Ben51]

The length the first 3 numbers have been trimmed to is my desired length for column A, However, if the number in column B is the same length or smaller than my desired length then i must mask the last 2 digits.

 

[FormR]

Hi, here is one possible option if I've understood correctly:

Excel 2013
	A	B
1	07.01.01.01	07.01.01.01.01
2	07.01.01.03	07.01.01.03.01.01
3	07.01.01.04	07.01.01.04.01
4	07.02	07.02.01
5	07.02.04	07.02.04.04
Sheet1

Cell Formulas
Range	Formula
A1	=LEFT(B1,FIND("|",SUBSTITUTE(B1,".","|",MIN(4,LEN(B1)-LEN(SUBSTITUTE(B1,".","")))))-1)

 

[miless2111s]

Assuming your format is always xx.xx.xx.xx.xx.xx the following will work:

A1=IF(LEN(B1)>11,LEFT(B1,11),LEFT(B1,LEN(B1)-3))

HTH

Miles

 

[Ben51]

Thankyou FormR,

Do you also have a formula to calculate the length of each number?
i.e.
B1 is a level 6 number (5 bits long +1)
B2 = 7
B3 = 6
B4 = 4
B5 = 5

 

[FormR]

Do you also have a formula to calculate the length of each number?
i.e.
B1 is a level 6 number (5 bits long +1)
B2 = 7
B3 = 6
B4 = 4
B5 = 5

Hi, I don't really understand what you mean here.

 

[FormR]

A1=IF(LEN(B1)>11,LEFT(B1,11),LEFT(B1,LEN(B1)-3))

Another option along similar lines to the above:

=LEFT(B1,MIN(11,LEN(B1)-3))

 

[FormR]

Do you also have a formula to calculate the length of each number?

Hi, if you still need help then maybe one of these options will help:

Excel 2013
	A	B	C	D
1	07.01.01.01	07.01.01.01.01	6	6
2	07.01.01.03	07.01.01.03.01.01	7	7
3	07.01.01.04	07.01.01.04.01	6	6
4	7.02	07.02.01	4	4
5	07.02.04	07.02.04.04	5	5
Sheet1

Cell Formulas
Range	Formula
C1	=LEN(B1)-LEN(SUBSTITUTE(B1,".",""))+2
D1	=1+(LEN(B1)+1)/3