How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?
 

Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks


Ship
Shiperton Henethe

How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?
 

On Apr 3, 1:41 pm, "ship" wrote:
Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks

ShipShipertonHenethe

I dont know what characters are going to be before or after the
number.
Basically I want to remove ALL characters apart from "0" to "9" and
"."
and then convert it into a numeric.

Any thoughts?


Ship

How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?
 

A user defined function similar to the following will work

Public Function stripNumbers(rng As Range)

Dim i As Integer

For i = 1 To Len(rng.Value)

If Mid(rng.Value, i, 1) = "0" And Mid(rng.Value, i, 1) <= "9"
Then
strNum = strNum & Mid(rng.Value, i, 1)
End If

Next

stripNumbers = CDbl(strNum)

End Function

You can email me @ to request a copy.

Regards,
Eddie
http://www.ExcelHelp.us

How can I convert a character string that contains letters & numbe
 

http://www.ozgrid.com/VBA/ExtractNum.htm
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ship" wrote:



Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks


Ship
Shiperton Henethe

How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?
 

On 3 Apr 2007 05:51:16 -0700, "ship" wrote:

On Apr 3, 1:41 pm, "ship" wrote:
Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks

ShipShipertonHenethe

I dont know what characters are going to be before or after the
number.
Basically I want to remove ALL characters apart from "0" to "9" and
"."
and then convert it into a numeric.

Any thoughts?


Ship






With your string in A1, you could use this formula, courtesy of Bob Phillips:

=LOOKUP(9.99999999999999E+307,--MID(
A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&
"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--ron