removing comma in data string

[jkrich]

I have a string of numbers, but they've been entered as:

357,041
332,224
341,038

etc.

Basically I'd just like to convert them to numbers so I can add,
subtract, etc. The problem is that as they are right now, they are
stored as text, and I can't figure out how to fix that (format cells >
number does nothing).

Please help.

 

[Dave Peterson]

Check your other post.

I have a string of numbers, but they've been entered as:

357,041
332,224
341,038

etc.

Basically I'd just like to convert them to numbers so I can add,
subtract, etc. The problem is that as they are right now, they are
stored as text, and I can't figure out how to fix that (format cells >
number does nothing).

Please help.

 

[CLR]

Try this..........
Enter a 1 in an unused cell
Copy it
Then Highlight the string to be changed and do PasteSpecial > Multiply

Vaya con Dios,
Chuck, CABGx3

 

[jkrich]

Try this..........
Enter a 1 in an unused cell
Copy it
Then Highlight the string to be changed and do PasteSpecial > Multiply

Vaya con Dios,
Chuck, CABGx3

Doesn't work. I've tried using the n() function to convert the text
into a number, but it just gives me 0.00

I've gotten rid of the commas now (using Find + Replace - Thanks
Dave!), but still can't format as anything but text.


-Jaremy

 

[Sandy Mann]

I think that you meant Paste Special > Add didn't you Chuck?

Time for another coffee

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[jkrich]

I think that you meant Paste Special > Add didn't you Chuck?

Time for another coffee

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

He meant multiply. Since multiplying any number by 1 = that number.
Unfortunately, when I do that I get a #VALUE error, since the cell in
question is actually text, not a number.

 

[RagDyer]

Where Dave?
Can't find it under this author.

 

[Sandy Mann]

357,041
Highlighting the ranges and then copying a blank, unused cell and Paste
Special > Add will get rid of the commas and convert to a number in one.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[CLR]

These things are always a problem, and the same procedure never seems to work
on all of them...........maybe try Data > TextToColumns, sometimes that will
break the spell.........then maybe the 1-multiply thing

Vaya con Dios,
Chuck, CABGx3

 

[Sandy Mann]

He meant multiply. Since multiplying any number by 1 = that number.

Unfortunately, when I do that I get a #VALUE error, since the cell in
question is actually text, not a number.

So he did. I was so sure that Chuck would have written copy a *black* cell
that I failed to read the post properly.

My appologies Chuck - it's me that needs the coffee!

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[CLR]

Another way to try.........

=(LEFT(A1,3)&RIGHT(A1,3))*1

Vaya con Dios,
Chuck, CABGx3

 

[Sandy Mann]

Having a bad day!

So he did. I was so sure that Chuck would have written copy a *black*
cell

was of course meant to be *BLANK* day!
--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[CLR]

No problem Sandy......come on over to my house and we'll brew up a new potfull.
My day is almost done here now, so I'm off to the Computer Store, <g>

Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

I have a string of numbers, but they've been entered as:

357,041
332,224
341,038

etc.

Basically I'd just like to convert them to numbers so I can add,
subtract, etc. The problem is that as they are right now, they are
stored as text, and I can't figure out how to fix that (format cells >
number does nothing).

Please help.

Wild guess -- you got these values from a web page.

If so, there may be a <nbsp> embedded.

Try this:

=--TRIM(SUBSTITUTE(A1,CHAR(160),""))


=
--ron

 

[jkrich]

Wild guess -- you got these values from a web page.

If so, there may be a <nbsp> embedded.

Try this:

=--TRIM(SUBSTITUTE(A1,CHAR(160),""))

=
--ron

Amazing. Thanks greatly, Ron! This fixed the problem 100%.

You're a life-saver.

 

[Dave Peterson]

In .programming.

Where Dave?
Can't find it under this author.

 

[RagDyer]

No wonder ... never go there!

 

[Ron Rosenfeld]

Amazing. Thanks greatly, Ron! This fixed the problem 100%.

You're a life-saver.

Glad to help. Thanks for the feedback.
--ron