removing comma in data string

  • Thread starter Thread starter jkrich
  • Start date Start date
J

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.
 
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.
Click to expand...
 
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
 
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
Click to expand...

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
 
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
 
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
Click to expand...

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.
 
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
 
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
 
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.
Click to expand...

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
 
Having a bad day!
So he did. I was so sure that Chuck would have written copy a *black*
cell
Click to expand...

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
 
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
 
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.
Click to expand...

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
 
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
Click to expand...

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

You're a life-saver.
 
Back
Top