How to delete "#"

[leezhihong]

How to delete "#" in range B1:B24, make its format like F1:F5?


<b>???? (2)</b>

Excel Workbook
	A	B	C	D	E	F
1	1	82-1#	42	2010.5.23		82-1
2	2	82-5#	42	2010.5.25		82-5
3	3	79-1#	42	2010.5.26		79-1
4	4	82-2#	42	2010.5.26		82-2
5	5	70-1#	44	2010.5.26		70-1
6	6	79-5#	42	2010.5.27
7	7	71-1#	42	2010.5.27
8	8	81-7#	42	2010.5.27
9	9	82-6#	42	2010.5.28
10	10	76-1#	40	2010.5.28
11	11	69-5#	42	2010.5.29
12	12	79-2#	42	2010.5.29
13	13	81-3#	42	2010.5.30
14	14	84-3#	40	2010.5.30
15	15	71-5#	42	2010.5.30
16	16	75-5#	40	2010.5.30
17	17	72-1#	42	2010.5.31
18	18	70-5#	44	2010.5.31
19	19	85-7#	35	2010.5.31
20	20	82-3#	42	2010.5.31
21	21	79-6#	42	2010.6.1
22	22	84-4#	40	2010.6.1
23	23	76-5#	40	2010.6.1
24	24	83-4#	42	2010.6.2
Sheet

 

[shg]

Ctrl+F (Find), put # in the Find box, nothing in the Replace box, press Replace All.

 

[sprimax]

You may try :
Select column B, then press Ctrl+F, find # and replace with blank

another simple way is :
in cell F1, type : =left(B1,4)

I do not know how to do it with vba.

Thank you

 

[leezhihong]

But it turn to be date format.


<b>????</b>

Excel Workbook
	A	B	C	D
1	1	Jan-82	42	2010.5.23
2	2	May-82	42	2010.5.25
3	3	Jan-79	42	2010.5.26
4	4	Feb-82	42	2010.5.26
5	5	Jan-70	44	2010.5.26
6	6	May-79	42	2010.5.27
7	7	Jan-71	42	2010.5.27
8	8	Jul-81	42	2010.5.27
9	9	Jun-82	42	2010.5.28
10	10	Jan-76	40	2010.5.28
11	11	May-69	42	2010.5.29
12	12	Feb-79	42	2010.5.29
13	13	Mar-81	42	2010.5.30
14	14	Mar-84	40	2010.5.30
15	15	May-71	42	2010.5.30
16	16	May-75	40	2010.5.30
17	17	Jan-72	42	2010.5.31
18	18	May-70	44	2010.5.31
19	19	Jul-85	35	2010.5.31
20	20	Mar-82	42	2010.5.31
21	21	Jun-79	42	2010.6.1
22	22	Apr-84	40	2010.6.1
23	23	May-76	40	2010.6.1
24	24	Apr-83	42	2010.6.2
Sheet

 

[shemayisroel]

Did you try sprimax =left(B1,4) solution.

That should work...