When importing data, a column does not recognize numbers

[paroduso]

Hi everyone,
im facing this problem...When importing data, column "C" does not recognize numbers. I allready made a vba code to make an advanced filter...but because of that the code doesnt work...if i convert the column to numbers it works fine...so what i need is, when past the values i need the column to automatically reconize the numbers...any formulas/vba code?

thank you very much.

 

[awoohaw]

If you're pasting data from a screen grab or web page, it may have zero length characters in what you're pasting.
You can test this by doing a

=CODE(MID(C2,SEQUENCE(Len(C2)),1))

If you see Code numbers outside of printable characters that is the culprit. Its pretty hard to clean up before pasting.
You could update your VBA code to strip the characters out.

if you don't have SEQUENCE function, you'll have to use an altered formula and drag across.


Char(160) is one such character:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2			=REPT("A",5)&CHAR(160)	Len	MID SEQUENCE
3			AAAAA	6	A	A	A	A	A
4					A	A	A	A	A
Sheet3

Cell Formulas
Range	Formula
C2	C2	=FORMULATEXT(C3)
C3	C3	=REPT("A",5)&CHAR(160)
D3	D3	=LEN(C3)
E3:J3	E3	=MID(C3,SEQUENCE(1,LEN(C3)),1)
E4:K4	E4	=IF(COLUMNS($E$4:E4)>LEN($C$3),"",MID($C$3,COLUMNS($E$4:E4),1))
Dynamic array formulas.

 

[awoohaw]

If you're pasting data from a screen grab or web page, it may have zero length characters in what you're pasting.
You can test this by doing a

=CODE(MID(C2,SEQUENCE(Len(C2)),1))

If you see Code numbers outside of printable characters that is the culprit. Its pretty hard to clean up before pasting.
You could update your VBA code to strip the characters out.

if you don't have SEQUENCE function, you'll have to use an altered formula and drag across.


Char(160) is one such character:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2			=REPT("A",5)&CHAR(160)	Len	MID SEQUENCE
3			AAAAA	6	A	A	A	A	A
4					A	A	A	A	A
Sheet3

Cell Formulas
Range	Formula
C2	C2	=FORMULATEXT(C3)
C3	C3	=REPT("A",5)&CHAR(160)
D3	D3	=LEN(C3)
E3:J3	E3	=MID(C3,SEQUENCE(1,LEN(C3)),1)
E4:K4	E4	=IF(COLUMNS($E$4:E4)>LEN($C$3),"",MID($C$3,COLUMNS($E$4:E4),1))
Dynamic array formulas.

I apologize for a second reply. I forgot to wrap the CODE function around the MID formula:

Book1
	C	D	E	F	G	H	I	J	K	L
1
2	=REPT("A",5)&CHAR(160)	Len	MID SEQUENCE
3	AAAAA	6	65	65	65	65	65	160		=CODE(MID(C3,SEQUENCE(1,LEN(C3)),1))
4			65	65	65	65	65	160		=IF(COLUMNS($E$4:E4)>LEN($C$3),"", CODE(MID($C$3,COLUMNS($E$4:E4),1)))
Sheet3

Cell Formulas
Range	Formula
C2	C2	=FORMULATEXT(C3)
C3	C3	=REPT("A",5)&CHAR(160)
D3	D3	=LEN(C3)
E3:J3	E3	=CODE(MID(C3,SEQUENCE(1,LEN(C3)),1))
E4:K4	E4	=IF(COLUMNS($E$4:E4)>LEN($C$3),"",CODE(MID($C$3,COLUMNS($E$4:E4),1)))
Dynamic array formulas.

 

[paroduso]

Hi awoohaw...thank you very much for the support...

there are things sometimes without explanation...even without excel recognizing the numbers...I wasted about 1 hour with formulas back and forth and just closed the file and opened it...the VBA code that I have pure and it just worked!!!

once again thank you.

 

[awoohaw]

Well, a fresh reboot of files is always one way to start fresh!

Best wishes!