Need help with Converting numbers

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I need to convert this text number 486' 6 into a real number. This
would equal $4.866 but 486' 6 is how commodities are read and
downloaded onto excel. Anyone know how to convert?
 
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"'","")," ",""))


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I need to convert this text number 486' 6 into a real number. This
| would equal $4.866 but 486' 6 is how commodities are read and
| downloaded onto excel. Anyone know how to convert?
|
 
The only way i know is to do a ctl H find and replace, ' and replace with .
then divide all by 100. Kind of ghetto but it works.
 
Try something like this:
=SUBSTITUTE(A1,"' ","")/1000

With A1: 486' 6
that formula returns 4.866

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
This work?
=TEXT(SUBSTITUTE(A2,"'",""),"$0.00")/1000
Click to expand...

Sorry it didn't work. Here's what I got.

A B C
01/03/2007 486' 6 $4.866 'Should look like this.
01/04/2007 477' 2
01/05/2007 481' 2
01/08/2007 479' 0
01/09/2007 471' 4
01/10/2007 472' 2
01/11/2007 481' 2
01/12/2007 505' 2
01/16/2007 492' 0

In column C this is what it should be converted to.

Ryan
 
Using your posted sample data....
Try this:
=SUBSTITUTE(MID(A1,FIND(" ",A1)+1,255),"' ","")/1000

If A1 contains: 01/03/2007 486' 6
the formula returns 4.866

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"'","")," ",""))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I need to convert this text number 486' 6 into a real number. This
| would equal $4.866 but 486' 6 is how commodities are read and
| downloaded onto excel. Anyone know how to convert?
|
Click to expand...

Worked great, thanks!

=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"'","")," ","")) Good formula I added /
1000 to get number I wanted thanks.
 
This formula results in $4.866:
=TEXT(SUBSTITUTE(A1,"'","")/1000,"$0.000")

If you want the number formatting of the column to handle the
formatting, simply use the formula below with the number format set to
currency with 3 decimal places:
=SUBSTITUTE(A1,"'","")/1000
 
<I added / 1000 to get number I wanted>

The 3 digits made me think the dot was a (European) thousands separator

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| > =VALUE(SUBSTITUTE(SUBSTITUTE(A1,"'","")," ",""))
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > |I need to convert this text number 486' 6 into a real number. This
| > | would equal $4.866 but 486' 6 is how commodities are read and
| > | downloaded onto excel. Anyone know how to convert?
| > |
|
| Worked great, thanks!
|
| =VALUE(SUBSTITUTE(SUBSTITUTE(A1,"'","")," ","")) Good formula I added /
| 1000 to get number I wanted thanks.
|
 
Back
Top