Number Format

[jeffdolton]

Hi, I have copied a series of numbers from a website but cannot get these figures to act as a number. I've tried formatting, copy and paste value, find and place but without luck.

Here is one of the numbers:

4 3 5870675289

Can you help please.

 

[Kerryx]

looks like there is a space in there after the number 3

 

[Alex Blakenburg]

Copy the below into a blank sheet. Then copy your number into A2 and show us what you see.
I am expecting UniCode numbers on Line 2 in the 8000 range.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Orig Nos	Position -->	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
2	4 3 5870675289		52	8202	51	8202	53	56	55	48	54	55	53	50	56	57	#VALUE!	#VALUE!
Sheet1 (2)

Cell Formulas
Range	Formula
C2:R2	C2	=UNICODE(MID($A2,C1,1))

 

[jeffdolton]

Copy the below into a blank sheet. Then copy your number into A2 and show us what you see.
I am expecting UniCode numbers on Line 2 in the 8000 range.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Orig Nos	Position -->	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
2	4 3 5870675289		52	8202	51	8202	53	56	55	48	54	55	53	50	56	57	#VALUE!	#VALUE!
Sheet1 (2)

Cell Formulas
Range	Formula
C2:R2	C2	=UNICODE(MID($A2,C1,1))

Mnay thnaks,

I've used a different number, this is what I get:

Orig Ni	Position	1​	2​	3​	4​	5​	6​	7​	8​
3 4 1 9 0 8 6 0		51​	8202​	52​	8202​	49​	8202​	57​	8202​

 

[jeffdolton]

looks like there is a space in there after the number 3

Hi, unfortunately there are no spaces in the number.

 

[Alex Blakenburg]

Do you know how to use VBA ?

• Make your problem sheet the active sheet.
• Then go to VBA by hitting Alt+<F11>
• If you can't see the immediate window then hit Ctrl+G
• Copy the below into the immediate window and hit enter

ActiveSheet.UsedRange.Replace ChrW(8202), ""

 

[jeffdolton]

Do you know how to use VBA ?

• Make your problem sheet the active sheet.
• Then go to VBA by hitting Alt+<F11>
• If you can't see the immediate window then hit Ctrl+G
• Copy the below into the immediate window and hit enter

ActiveSheet.UsedRange.Replace ChrW(8202), ""

The immediate window was greyed out. Do I insert the code into a new module?

 

[Alex Blakenburg]

Sounds a bit odd.
But is you are happy to create a new module then sure create a new module and paste in:
then run it.

Sub RemoveCode8202()

    ActiveSheet.UsedRange.Replace ChrW(8202), ""

End Sub

 

[jeffdolton]

Sounds a bit odd.
But is you are happy to create a new module then sure create a new module and paste in:
then run it.

Sub RemoveCode8202()

    ActiveSheet.UsedRange.Replace ChrW(8202), ""

End Sub

Perfect, problem solved - thank you

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.