Formula needed

[baggarwal]

Hi All:

I have a column that has the following data:
1161
1162
1163
1164

I would like to create a column to the left of it that will keep the first three digits the same and replace the fourth digit with a zero.

The desired result is
1160 1161
1160 1162
1160 1163
1160 1164

Can someone show me a formula that will do this?

 

[njimack]

=(left(b1,3)+0)*10

 

[baggarwal]

Hi Neil, this formula worked like a charm. Can you just explain how the formula works. I am just not sure why the +0 and *10.

 

[T. Valko]

Hi All:

I have a column that has the following data:
1161
1162
1163
1164

I would like to create a column to the left of it that will keep the first three digits the same and replace the fourth digit with a zero.

The desired result is
1160 1161
1160 1162
1160 1163
1160 1164

Can someone show me a formula that will do this?

Here's another one...

Book1
	A	B
2	1160	1161
3	1160	1162
4	1120	1121
5	2250	2258
Sheet1

Formula entered in A2 and copied down:

=--(LEFT(B2,3)&0)

 

[njimack]

Hi Neil, this formula worked like a charm. Can you just explain how the formula works. I am just not sure why the +0 and *10.

+0 because LEFT returns a text value, so adding zero converts this back to a numerical value.

Multiplying by 10 is to get a zero at the end.

The other solution you were provided with is the alternative approach, but the result would be a text value rather than a number.

 

[CDNWolf]

Hi Neil, this formula worked like a charm. Can you just explain how the formula works. I am just not sure why the +0 and *10.

It seems that the +0 wuld do nothing but the *10 makes sense...

 

[T. Valko]

The other solution you were provided with is the alternative approach, but the result would be a text value rather than a number.

Are you referring to my suggestion?

If so, my suggestion returns a numeric number.

=--(LEFT(B2,3)&0)

The double unary minus has the same effect as adding 0.

 

[njimack]

Oh yeah, sorry. Didn't spot the 2 minus signs.

 

[T. Valko]

It seems that the +0 wuld do nothing but the *10 makes sense...

Try this little test.

A1 = 12345

If you didn't apply any formatting then the entry should be aligned to the right of the cell.

Enter this formula in B1:

=LEFT(A1,3)

The result you'll get is 123. Looks like a number but it's not. It's a TEXT string. If you didn't apply any formatting to the cell then the cell is aligned to the left.

That's how you can tell at a quick glance whether a cell contains text or a number (provided no special formatting has been applied). Numbers align to the right while text aligns to the left.

Now, enter this formula in C1:

=ISTEXT(B1)

The result is TRUE. Cell B1 contains a TEXT value.

Now, enter these formulas in D1 and D2:

=B1+0
=--B1

Notice how the results of the formulas are now aligned to the right? This means the TEXT number 123 has been converted to the NUMERIC number 123.

We can verify this using one of these formulas:

=COUNT(D1)
=COUNT(D2)

=ISNUMBER(D1)
=ISNUMBER(D2)

COUNT will return 1 and ISNUMBER will return TRUE.