Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | 12345 | 123 | =LEFT(A1,3) | |||
2 | 12345 | 124 | =LEFT(A2,2)&MID(A2,4,1) | |||
3 | 12345 | 125 | =LEFT(A3,2)&RIGHT(A3,1) | |||
4 | 12345 | 134 | =LEFT(A4,1)&MID(A4,3,2) | |||
5 | 12345 | 135 | =LEFT(A5,1)&MID(A5,3,1)&RIGHT(A5,1) | |||
6 | 12345 | 234 | =MID(A6,2,3) | |||
7 | 12345 | 235 | =MID(A7,2,2)&RIGHT(A7,1) | |||
8 | 12345 | 245 | =MID(A8,2,1)&RIGHT(A8,2) | |||
9 | 12345 | 345 | =RIGHT(A9,3) | |||
Sheet1 |
ADVERTISEMENT
Does anyone know how to remove numbers in excel, I need to to remove 2 last digits from a 5 digit zip code making it into 3 digit zip code.
Thank you so much
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | number | 12345 | 123 | =INT(A1/100) | ||
2 | text | 12345 | 123 | =INT(A2/100) | ||
3 | 246 | =SUM(B1:B2) | ||||
4 | ||||||
5 | number | 12345 | 12345 | =INT(A5) | ||
6 | text | 12345 | 12345 | =INT(A6) | ||
7 | 24690 | =SUM(B5:B6) | ||||
Sheet1 |
Tushar, your formula seems to work even if the numbers are stored as text.